Converts MongoDB queries to postgresql queries for jsonb fields.
npm install mongo-query-to-postgres-jsonbwhere clause for data stored in a jsonb field.select clauses and for update queries.This tool is used by pgmongo which intends to provide a drop-in replacement for MongoDB.
``sh`
npm install mongo-query-to-postgres-jsonb
`js`
var mToPsql = require('mongo-query-to-postgres-jsonb')
var query = { field: 'value' }
var sqlQuery = mToPsql('data', query)
`js`
var mToPsql = require('mongo-query-to-postgres-jsonb')
#### 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.
#### projectionQuery
Object specifying which a subset of documents to return. Note: advanced projection fields are not yet supported.
#### 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.
#### sortQuery
Object containing desired ordering
#### forceNumericSort
Cast strings to number when sorting.
| Languages | MongoDB | Postgres |
|------------|-------------------------------|---------------------------------------------------------------------------------|
| Where | { 'names.0': 'thomas' } | (data->'names'->>0 = 'thomas') |
| 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 |
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 tool implements it in SQL using a subquery, so it will likely not be the efficient on large datasets.
To enable subfield matching, you can pass a third parameter which is either an array of dotted paths that will be assumed
to potentially be arrays or true if you want it to assume any field can be an array.
Example document:
`js`
{
"courses": [{
"distance": "5K"
}, {
"distance": "10K"
}]
]
Example queries to match:js`
mongoToPostgres('data', { 'courses.distance': '5K' }, ['courses'])
mongoToPostgres('data', { 'courses.distance': '5K' }, true)
This then creates a PostgreSQL query like the following:
`
(data->'courses'->>'distance'='5K'
OR EXISTS (SELECT * FROM jsonb_array_elements(data->'courses')
WHERE jsonb_typeof(data->'courses')='array' AND value->>'distance'='5K'))
Note: nested paths are not yet supported, so passing ['courses', 'courses.distance'] won't support checking both.
The first matching path is the one that will be used.