Mongo-Postgres Query Converter

MongoDB query documents are quite powerful. This brings part of that usefulsness to PostgreSQL by letting you query in a similar way. This tool converts a Mongo query to a PostgreSQL "where" clause for data stored in a jsonb field. It also has additional converters for Mongo projections which are like "select" clauses and for update queries.

The goal of this is to eventually provide an adapter which lets Postgres serve as a drop in replacement for Mongo, but that is not there yet. Currently the project has many of the underlying conversions that will be required to do this. For that project, see pgmongo.

Select Query Example 1

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

becomes the following Postgres query

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

Select Query Example 2

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

becomes the following Postgres query

((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')
var query = { field: 'value' }
var sqlQuery = mongoToPostgres('data', query)
console.log(sqlQuery)

The first parameter, "data", is the name of your jsonb column in your postgres table. The second parameter is the Mongo style query. There is an optional third parameter explained in the next section.

Projection Example

mongoToPostgres.convertSelect('data', { field: 1 })

becomes the following Postgres query

jsonb_build_object('field', data->'field', '_id', data->'_id')'

Update Example

mongoToPostgres.convertUpdate('data', {
  $set: { active: true },
  $inc: { purchases: 2 }
})

becomes the following Postgres query

jsonb_set(jsonb_set(data,'{active}','true'::jsonb),'{purchases}',to_jsonb(Cast(data->>'purchases' as numeric)+2))

Sort Example

mongoToPostgres.convertSort('data', {
  age: -1,
  'first.name': 1
})

becomes the following Postgres query

data->'age' DESC, data->'first'->'name' ASC

Select: Match a Field Without Specifying Array Index

• Mongo Docs

You can have a document with an array of objects that you want to match when any one of the elements in the array matches. This is implemented in SQL using a subquery so it may not be the most efficient.

Example document.

{
  "courses": [{
    "distance": "5K"
  }, {
    "distance": "10K"
  }]
}

Unlike Mongo, this tool doesn't know which fields are arrays and requires you to supply a list optionally as a third parameter. Example query to match when there is a course with a distance of "5K".

mongoToPostgres('data', { 'courses.distance': '5K' }, ['courses'])

Supported Features

• $eq, $gt, $gte, $lt, $lte, $ne
• $or, $not, $nin
• $in, $nin
• $elemMatch
• $regex
• $type
• $size
• $exists
• $mod

Todo

• Filtering
  • $all
  • $expr
  • Bitwise Operators
• Update
  • $pop
  • $currentDate
  • $setOnInsert

Cannot Support

• $where

See also

• PostgreSQL json/jsonb functions and operators
• PostgreSQL json documentation
• MongoDB query documention
• PostgreSQL Array Functions
• JSON array to PostgreSQL Array