Skip to content
Converts MongoDB queries to postgresql queries for jsonb fields.
Branch: master
Clone or download
Fetching latest commit…
Cannot retrieve the latest commit at this time.
Type Name Latest commit message Commit time
Failed to load latest commit information.
test Update readme and remove dependencies Sep 1, 2019
.eslintrc.js Improve array queries and other minor changes. Jul 6, 2018
index.js Update readme and remove dependencies Sep 1, 2019
select.js Improve array queries and other minor changes. Jul 6, 2018
update.js Update readme and remove dependencies Sep 1, 2019
util.js Add $text support, improve $type, bump version Jul 11, 2018

Mongo-Postgres Query Converter

MongoDB query documents are quite powerful. This brings that usefulness 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.

This tool is used by pgmongo which intends to provide a drop-in replacement for MongoDB.


npm install mongo-query-to-postgres-jsonb

Simple Usage

var mToPsql = require('mongo-query-to-postgres-jsonb')
var query = { field: 'value' }
var sqlQuery = mToPsql('data', query)


var mToPsql = require('mongo-query-to-postgres-jsonb')

mToPsql(sqlField, mongoQuery, [arrayFields])


This is the name of your jsonb column in your postgres table which holds all the data.


An object containing MongoDB query operators.


This tool doesn't know which fields are arrays so you can optionally specify a list of dotted paths which should be treated as an array.

mToPsql.convertSelect(sqlField, projectionQuery)


Object specifying which a subset of documents to return. Note: advanced projection fields are not yet supported.

mToPsql.convertUpdate(sqlField, updateQuery, [upsert])


Object containing MongoDB operations to apply to the documents.


Indicate that the query is being used for upserting. This will create a safer query that works if the original document doesn't already exist.

mToPsql.convertSort(sqlField, sortQuery, [forceNumericSort])


Object containing desired ordering


Cast strings to number when sorting.


Languages MongoDB Postgres
Where { '': 'provo' } (data->'address'->>'city' = 'provo')
Where { $or: [ { qty: { $gt: 100 } }, { price: { $lt: 9.95 } } ] } ((data->'qty'>'100'::jsonb) OR (data->'price'<'9.95'::jsonb))
Projection { field: 1 } jsonb_build_object('field', data->'field', '_id', data->'_id')'
Update { $set: { active: true } } jsonb_set(data,'{active}','true'::jsonb)
Update { $inc: { purchases: 2 } } jsonb_set(data,'{purchases}',to_jsonb(Cast(data->>'purchases' as numeric)+2))
Sort { age: -1, '': 1} data->'age' DESC, data->'first'->'name' ASC

Advanced Select: Match a Field Without Specifying Array Index

With MongoDB, you can search a document with a subarray of objects that you want to match when any one of the elements in the array matches. This tools implements it in SQL using a subquery so it is not be the most efficient.

Example document.

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

Example query to match:

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

Supported Features


Cannot Support

See also

You can’t perform that action at this time.