orgoldfus / sql2mongo

Use SQL to query MongoDB

sql2mongo

Use SQL syntax to query MongoDB

Installation

npm install sql2mongo

Usage

getMongoQuery

Parses an SQL WHERE clause to a mongo query object.

Example:

const { getMongoQuery } = require("sql2mongo");

const mongoQuery = getMongoQuery(`
  show = "Friends" OR
  (city = "New York" AND
  year BETWEEN 2005 AND 2014 AND
  name IN ("Ted", "Marshall", "Barney"))
`)

Result:

{
  $or: [
    { show: "Friends" },
    {
      city: "New York",
      year: { $gt: 2005, $lt: 2014 },
      name: { $in: ["Ted", "Marshall", "Barney"] },
    }
  ]
}

Nested objects

If you need to query a nested object, use the NESTED function inside your query. The NESTED function receives a string with a WHERE clause for the nested object.

Example: If you would like to query the following nested document:

{
  item: 'journal',
  qty: 25,
  size: { h: 14, w: 21, uom: 'cm' },
  status: 'A'
}

You can use the NESTED function like this:

const { getMongoQuery } = require("sql2mongo");

const mongoQuery = getMongoQuery(`
  size = NESTED("h = 14 AND w > 20")
`)

Result:

{
  size: {
    h: 14,
    w: { $gt: 20 }
  }
}

Otherwise, you can use the dot annotation:

const { getMongoQuery } = require("sql2mongo");

const mongoQuery = getMongoQuery(`
  size.h = 14 AND 
  size.w > 20
`)

Result:

{
  "size.h": 14,
  "size.w": { $gt: 20 }
}

TODO

• Add support for all DML commands (insert, update, etc.)

Build

• Run npm run build to build the package.

LICENSE

MIT

sql2mongo's Issues

AND Queries With Multiple Expressions Specifying the Same Field do not work

The following does not return as expected:

getMongoQuery('field > 1 AND field < 5')
> { field: { "$lt": 5 } }

Expected result:

{ "$and": [ { field: { "$gt": 1 }}, { field: { "$lt": 5 } } ] }
// OR
{ field: { "$gt": 1, "$lt": 5 } }

Null expression type is not supported

The expression: getMongoQuery('field IN (NULL, 3)')
fails with: Uncaught Error: Unsupported expression type: Null

However, using the js-sql-parser library, it does parse to valid AST:
sqlParser.parse('SELECT * FROM table WHERE field IN (NULL, 3)')

Add support for Boolean types and '<>' operator

Thanks for the great library!

This is an enhancement request for support of Boolean types and the '<>' operator. I added the necessary logic in #17.

Thanks for your consideration.

Not getting proper response.

converting "Name LIKE 'Update Price%'" returns { Name: { '$regex': 'Update Price%' } }