Mongo-Postgres Query Converter

MongoDB query documents are useful, but only work with Mongo. This converts a Mongo query to a PostgreSQL WHERE clause on data stored in a json/jsonb field.

Example 1

{ 'address.city': 'provo',
  name: 'Thomas',
  age: { '$gte': '30' } }

becomes

(data->'address'->>'city'='provo') and (data->>'name'='Thomas') and (data->>'age'>='30')

Example 2

{
     $or: [ { qty: { $gt: 100 } }, { price: { $lt: 9.95 } } ]
}

becomes

((data->'qty'>'100'::jsonb) OR (data->'price'<'9.95'::jsonb))

Getting Started

npm install mongo-query-to-postgres-jsonb

var mongoToPostgres = require('mongo-query-to-postgres-jsonb');
mongoToPostgres('data', query)

The first parameter is the name of your jsonb column in your postgres table. The second parameter is the Mongo style query.

Supported Features

• $eq, $gt, $gte, $lt, $lte, $ne
• $or, $not
• $in, $nin
• $elemMatch
• $regex

Todo

• $nor
• $exists
• $size
• $type
• Match an array element

Can't support

• Match a Field Without Specifying Array Index

See also

• PostgreSQL json/jsonb functions and operators
• PostgreSQL json documentation
• MongoDB query documention