Project

mongery

0.02
No commit activity in last 3 years
No release in over 3 years
Convert MongoDB query to Arel for PostgreSQL + JSON
2005
2006
2007
2008
2009
2010
2011
2012
2013
2014
2015
2016
2017
2018
2019
2020
2021
2022
 Dependencies

Development

>= 4.0.0
~> 1.7
>= 0
~> 10.0
>= 0

Runtime

>= 4.0.2
 Project Readme

Mongery

Mongery translates MongoDB query to Arel AST for Postgres with JSON columns, ready to use with ActiveRecord connection.

This could be useful in situations where you migrate off of MongoDB backend to Postgres without touching the application code, or to provide MongoDB query syntax as a JSON API to your Postgres backend.

Limitation

Currently, Mongery supports the limited set of queries to use with PostgreSQL 9.3. Most query on JSON data will end up with the full table scan, unless you manually create expression index for the JSON path you want to query on.

See the spec file in spec directory for the supported conversion.

Usage

Create a table with the following structure. The name of the table could be anything.

CREATE TABLE objects (
  id varchar(32) not null primary key,
  data json not null,
  updated_at timestamp DEFAULT current_timestamp
)

Currently Mongery assumes the id values are stored as _id key duplicated in the json data as well. This can be customized in the future updates.

builder = Mongery::Builder.new(:objects)

builder.find({ _id: 'abcd' }).limit(1).to_sql
# => SELECT data FROM objects WHERE id = 'abcd' LIMIT 1

builder.find({ age: {"$gte" => 21 } }).sort({ name: -1 }).to_sql
# => SELECT data FROM objects WHERE (data#>>'{age}')::integer >= 21 ORDER BY data#>>'{name}' DESC

builder.find({ "address.city": {"$in" => ["San Francisco", "Tokyo"] } })
# => SELECT data FROM objects WHERE data#>>'{address,city}' IN ("San Francisco", "Tokyo")

builder.insert({ _id: 'foobar' }).to_sql
# => INSERT INTO "objects" ("id", "data") VALUES ('foobar', '{"_id":"foobar"}')

builder.find({ age: {"$gte" => 21 } }).update({ name: "John" }).to_sql
# => UPDATE "objects" SET "data" = '{"name":"John"}' WHERE (data#>>'{age}')::integer >= 21

builder.find({ age: {"$eq" => 55 } }).delete.to_sql
# => DELETE FROM "objects" WHERE (data#>>'{age}')::integer = 21

Type Casting

By default, types are dynamically casted depending on the query values.

builder.find({ age: {"$eq" => 55 } }).to_sql
# => SELECT FROM "objects" WHERE (data#>>'{age}')::integer = 21

Mongery has an experimental support for JSON Schema. You can pass JsonSchema::Schema object created with json_schema gem to the Mongery::Builder constructor, to make it cast the query type based on the schema rather than the bound value.

schema_data = {
  "type" => "object",
  "properties" => {
    "name" => {
      "type" => "string"
    },
  },
}
schema = JsonSchema.parse!(schema_data)

builder = Mongery::Builder.new(:objects, ActiveRecord::Base, schema)

See Also

License

MIT

Copyright

Tatsuhiko Miyagawa