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.
Installation
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)
API
var mToPsql = require('mongo-query-to-postgres-jsonb')
mToPsql(sqlField, mongoQuery, [arrayFields])
sqlField
This is the name of your jsonb column in your postgres table which holds all the data.
mongoQuery
An object containing MongoDB query operators.
arrayFields
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)
projectionQuery
Object specifying which a subset of documents to return. Note: advanced projection fields are not yet supported.
mToPsql.convertUpdate(sqlField, updateQuery, [upsert])
updateQuery
Object containing MongoDB operations to apply to the documents.
upsert
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])
sortQuery
Object containing desired ordering
forceNumericSort
Cast strings to number when sorting.
Examples
Languages | MongoDB | Postgres |
---|---|---|
Where | { 'address.city': '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, 'first.name': 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
- $eq, $gt, $gte, $lt, $lte, $ne
- $or, $not, $in, $nin
- $elemMatch
- $regex, $type, $size, $exists, $mod, $all
Todo
- Filtering
- Update