How to use mongo-query-to-postgres-jsonb - 9 common examples
To help you get started, we’ve selected a few mongo-query-to-postgres-jsonb examples, based on popular ways it is used in public projects.
Secure your code as it's written. Use Snyk Code to scan source code in minutes - no build needed - and fix issues immediately.
thomas4019 / pgmongo / index.js View on Github 
}
if (doc.limit) {
data.n = Math.min(Math.abs(doc.limit), data.n)
}
} catch (e) {
console.error(e)
}
socket.write(build(reqId, data, data))
return true
}
if (commandName === 'update') {
const update = doc.updates[0]
const where = mongoToPostgres('data', update.q, getArrayPaths(rawCollectionName))
let res
try {
const newValue = mongoToPostgres.convertUpdate('data', update.u)
// TODO (handle multi)
await createTable(collectionName)
res = await doQuery(databaseName, `UPDATE ${collectionName} SET data = ${newValue} WHERE ${where}`)
if (res.rowCount === 0 && update.upsert) {
const changes = update.u || {}
if (mongoToPostgres.countUpdateSpecialKeys(changes) === 0) {
// TODO: expand dot notation
_.assign(changes, update.q)
} else {
changes['$set'] = changes['$set'] || {}
_.assign(changes['$set'], update.q)
}
const newValue = mongoToPostgres.convertUpdate('data', changes, true)
async function insert() {
const res = await doQuery(databaseName, `INSERT INTO ${collectionName} VALUES (${newValue})`)
const data = { n: res.rowCount, nInserted: res.rowCount, updatedExisting: false, ok: 1 }let res
try {
const newValue = mongoToPostgres.convertUpdate('data', update.u)
// TODO (handle multi)
await createTable(collectionName)
res = await doQuery(databaseName, `UPDATE ${collectionName} SET data = ${newValue} WHERE ${where}`)
if (res.rowCount === 0 && update.upsert) {
const changes = update.u || {}
if (mongoToPostgres.countUpdateSpecialKeys(changes) === 0) {
// TODO: expand dot notation
_.assign(changes, update.q)
} else {
changes['$set'] = changes['$set'] || {}
_.assign(changes['$set'], update.q)
}
const newValue = mongoToPostgres.convertUpdate('data', changes, true)
async function insert() {
const res = await doQuery(databaseName, `INSERT INTO ${collectionName} VALUES (${newValue})`)
const data = { n: res.rowCount, nInserted: res.rowCount, updatedExisting: false, ok: 1 }
socket.write(build(reqId, data, {}))
}
await tryOrCreateTable(insert, databaseName, collectionName)
return true
} else {
lastResult.n = res.rowCount
socket.write(build(reqId, lastResult, lastResult))
return true
}
} catch (e) {
console.error(e)
if (e.message.includes('_id')) {}
socket.write(build(reqId, data, data))
return true
}
if (commandName === 'update') {
const update = doc.updates[0]
const where = mongoToPostgres('data', update.q, getArrayPaths(rawCollectionName))
let res
try {
const newValue = mongoToPostgres.convertUpdate('data', update.u)
// TODO (handle multi)
await createTable(collectionName)
res = await doQuery(databaseName, `UPDATE ${collectionName} SET data = ${newValue} WHERE ${where}`)
if (res.rowCount === 0 && update.upsert) {
const changes = update.u || {}
if (mongoToPostgres.countUpdateSpecialKeys(changes) === 0) {
// TODO: expand dot notation
_.assign(changes, update.q)
} else {
changes['$set'] = changes['$set'] || {}
_.assign(changes['$set'], update.q)
}
const newValue = mongoToPostgres.convertUpdate('data', changes, true)
async function insert() {
const res = await doQuery(databaseName, `INSERT INTO ${collectionName} VALUES (${newValue})`)
const data = { n: res.rowCount, nInserted: res.rowCount, updatedExisting: false, ok: 1 }
socket.write(build(reqId, data, {}))
}
await tryOrCreateTable(insert, databaseName, collectionName)
return true
} else {async function crud(socket, reqId, databaseName, commandName, doc, build) {
const normalizedCommandName = commandName.toLowerCase()
let rawCollectionName = doc[commandName] || doc[normalizedCommandName]
let collectionName = '"' + rawCollectionName + '"'
if (commandName === 'distinct') {
if ((doc.query && typeof doc.query !== 'object') || typeof doc.key !== 'string') {
socket.write(build(reqId, { ok: 0, errmsg: '"query" had the wrong type. Expected object or null,', code: 14 }, {}))
//throw new Error('\\"query\\" had the wrong type. Expected object or null, found ' + typeof doc.query)
return true
}
const filter = doc.query || {}
let where = safeWhereConversion(filter, rawCollectionName)
const distinctField = mongoToPostgres.pathToText(['data'].concat(doc.key.split('.')), false)
const arrayCondition = `jsonb_typeof(${distinctField})='array'`
const query1 = `SELECT DISTINCT ${distinctField} AS data FROM ${collectionName} WHERE ${where} AND NOT ${arrayCondition}`
const query2 = `SELECT DISTINCT jsonb_array_elements(${distinctField}) AS data FROM ${collectionName} WHERE ${where} AND ${arrayCondition}`
const query = `${query1} UNION ${query2}`
const res = await tryOrCreateTable(async () => await doQuery(databaseName, query), databaseName, collectionName)
const rows = res.rows.map((row) => convertToBSON(row.data))
debug('pgmongo:rows')(rows)
const data = { values: rows, ok: 1 }
socket.write(build(reqId, data, {}))
return true
}
if (commandName === 'find') {
const filter = doc.filter
const where = safeWhereConversion(filter, rawCollectionName)
let select = mongoToPostgres.convertSelect('data', doc.projection)const pgPath = keys.map(function(key) {
const path = ['data'].concat(key.split('.'))
return mongoToPostgres.pathToText(path, false)
}).join(', ')
let indexQuery = `CREATE INDEX "${index.name}" ON ${collectionName} USING gin ((${pgPath}));`const distinctField = mongoToPostgres.pathToText(['data'].concat(doc.key.split('.')), false)
const arrayCondition = `jsonb_typeof(${distinctField})='array'`
const query1 = `SELECT DISTINCT ${distinctField} AS data FROM ${collectionName} WHERE ${where} AND NOT ${arrayCondition}`
const query2 = `SELECT DISTINCT jsonb_array_elements(${distinctField}) AS data FROM ${collectionName} WHERE ${where} AND ${arrayCondition}`
const query = `${query1} UNION ${query2}`
const res = await tryOrCreateTable(async () => await doQuery(databaseName, query), databaseName, collectionName)
const rows = res.rows.map((row) => convertToBSON(row.data))
debug('pgmongo:rows')(rows)
const data = { values: rows, ok: 1 }
socket.write(build(reqId, data, {}))
return true
}
if (commandName === 'find') {
const filter = doc.filter
const where = safeWhereConversion(filter, rawCollectionName)
let select = mongoToPostgres.convertSelect('data', doc.projection)
let query = `SELECT ${select} FROM ${collectionName}`
if (where !== 'TRUE') {
query += ` WHERE ${where}`
}
if (doc.sort) {
query += ' ORDER BY ' + mongoToPostgres.convertSort('data', doc.sort, doc.collation && doc.collation.numericOrdering)
}
if (doc.limit) {
query += ' LIMIT ' + Math.abs(doc.limit)
}
if (doc.skip) {
if (doc.skip < 0) {
socket.write(build(reqId, { ok: 0, errmsg: 'negative skip not allowed' }, {}))
return true
}debug('pgmongo:rows')(rows)
const data = { values: rows, ok: 1 }
socket.write(build(reqId, data, {}))
return true
}
if (commandName === 'find') {
const filter = doc.filter
const where = safeWhereConversion(filter, rawCollectionName)
let select = mongoToPostgres.convertSelect('data', doc.projection)
let query = `SELECT ${select} FROM ${collectionName}`
if (where !== 'TRUE') {
query += ` WHERE ${where}`
}
if (doc.sort) {
query += ' ORDER BY ' + mongoToPostgres.convertSort('data', doc.sort, doc.collation && doc.collation.numericOrdering)
}
if (doc.limit) {
query += ' LIMIT ' + Math.abs(doc.limit)
}
if (doc.skip) {
if (doc.skip < 0) {
socket.write(build(reqId, { ok: 0, errmsg: 'negative skip not allowed' }, {}))
return true
}
query += ' OFFSET ' + doc.skip
}
let res
try {
res = await tryOrCreateTable(async () => await doQuery(databaseName, query), databaseName, collectionName)
} catch (e) {
console.error(e) const newValues = doc.documents.map((values) => '(' + mongoToPostgres.convertUpdate('data', values) + ')')
async function insert() {query += orderby.map((ordering) => {
return mongoToPostgres.convertDotNotation('data', ordering[0]) + (ordering[1] > 0 ? ' ASC' : ' DESC')
}).join(', ')
}mongo-query-to-postgres-jsonb
Converts MongoDB queries to postgresql queries for jsonb fields.
