@hmudimi/s2m v1.1.18

sql-to-mongo

Converts M-SQL Queries to Mongo find statements or aggregation pipelines.

What is M-SQL?

M-SQL is a specific way to use MySQL style queries tailored to MongoDB functions and a multilevel document paradigm.

Notes

• Supports Mongo 3.6 or greater
• Follows mySQL Syntax
• As with MongoDB column names and strings are case-sensitive.

Currently Unsupported SQL Statements

• Over
• CTE's
• Pivot
• Union

Installation

npm i @synatic/sql-to-mongo --save

Usage

const SQLMongoParser = require('@synatic/sql-to-mongo');

parseSQL(sqlStatement)

Parses the given SQL statement to an aggregate or query depending on if a straight query is possible.

const SQLMongoParser=require('@synatic/sql-to-mongo');
console.log(JSON.stringify(SQLMongoParser.parseSQL("select id from `films` where `id` > 10 limit 10"),null,4));

{
    "limit": 10,
    "collection": "films",
    "projection": {
        "id": "$id"
    },
    "query": {
        "id": {
            "$gt": 10
        }
    },
    "type": "query"
}

const SQLMongoParser=require('@synatic/sql-to-mongo');
console.log(JSON.stringify(SQLMongoParser.makeMongoAggregate("select id from `films` where `id` > 10 group by id"),null,4));
{
    "pipeline": [
        {
            "$match": {
                "id": {
                    "$gt": 10
                }
            }
        },
        {
            "$group": {
                "_id": {
                    "id": "$id"
                }
            }
        },
        {
            "$project": {
                "id": "$_id.id",
                "_id": 0
            }
        }
    ],
    "collections": [
        "films"
    ]
}

const SQLParser = require('./lib/SQLParser');
const {MongoClient} = require('mongodb');

(async () => {
    try {
        client = new MongoClient('mongodb://127.0.0.1:27017');
        await client.connect();
        const db = client.db('sql-to-mongo-test');

        const parsedSQL = SQLParser.parseSQL('select id from `films` limit 10');
        if (parsedSQL.type === 'query') {
            console.log(
                await db
                    .collection(parsedSQL.collection)
                    .find(parsedSQL.query || {}, parsedSQL.projection || {})
                    .limit(parsedSQL.limit || 50)
                    .toArray()
            );
        } else if (parsedSQL.type === 'aggregate') {
            console.log(
                await db
                    .collection(parsedSQL.collections[0])
                    .aggregate(parsedSQL.pipeline)
                    .toArray()
            );
        }
    } catch (exp) {
        console.error(exp);
    }
})();

makeMongoQuery(sqlStatement)

Generates a mongo query if possible. Will throw an exception if not possible.

const SQLMongoParser=require('@synatic/sql-to-mongo');
console.log(SQLMongoParser.makeMongoQuery("select id from `films` where id > 10 limit 10"));

{
    "limit": 10,
    "collection": "films",
    "projection": {
        "id": "$id"
    },
    "query": {
        "id": {
            "$gt": 10
        }
    }
}

makeMongoAggregate(sqlStatement)

Generates a mongo aggregate.

const SQLMongoParser=require('@synatic/sql-to-mongo');
console.log(JSON.stringify(SQLMongoParser.makeMongoAggregate("select id from `films` group by id"), null, 4));

{
    "pipeline": [
        {
            "$group": {
                "_id": {
                    "id": "$id"
                }
            }
        },
        {
            "$project": {
                "id": "$_id.id",
                "_id": 0
            }
        }
    ],
    "collections": [
        "films"
    ]
}

canQuery

Returns whether a statement can be queried or an aggregate must be used.

const SQLMongoParser = require('@synatic/sql-to-mongo');

console.log(SQLMongoParser.canQuery('select id from `films`'));
//true

console.log(SQLMongoParser.canQuery('select id from `films` group by id'));
//false

parseSQLtoAST(sqlStatement)

Parses a SQL statement to an AST (abstract syntax tree)

const SQLMongoParser=require('@synatic/sql-to-mongo');

const ast=SQLMongoParser.parseSQLtoAST("select id from `films`");
console.log(JSON.stringify(ast, null, 4));

{
    "tableList": [
        "select::null::films"
    ],
    "columnList": [
        "select::null::id"
    ],
    "ast": {
        "with": null,
        "type": "select",
        "options": null,
        "distinct": null,
        "columns": [
            {
                "expr": {
                    "type": "column_ref",
                    "table": null,
                    "column": "id"
                },
                "as": null
            }
        ],
        "from": [
            {
                "db": null,
                "table": "films",
                "as": null
            }
        ],
        "where": null,
        "groupby": null,
        "having": null,
        "orderby": null,
        "limit": null,
        "for_update": null
    }
}

M-SQL

Requires as for functions and sub queries

select abs(-1) as `absId` from `customers`

as on table requires prefixing

select c.* from customers as c

Always prefix on joins

Supported SQL Statements

Functions in WHERE statements require explicit definition and can't use a computed field

--Correct
select `Address.City` as City,abs(`id`) as absId from `customers` where `First Name` like 'm%' and abs(`id`) > 1 order by absId

--Won't work
select `Address.City` as City,abs(`id`) as absId from `customers` where `First Name` like 'm%' and absId > 1

ORDER BY requires field to be part of select

--Correct
select `Address.City` as City,abs(`id`) as absId from `customers` where `First Name` like 'm%' and abs(`id`) > 1 order by absId

--Won't work
select `Address.City` as City from `customers` where `First Name` like 'm%' and abs(`id`) > 1 order by abs(`id`)

Limit and Offset

Supports MySQL style limits and offset that equates to limit and skip

select (select * from Rentals) as t from `customers` limit 10 offset 2

Merge Fields into Object

Only available in aggregate Select without table

Create a new Object

select (select id,`First Name` as Name) as t  from customers

Create a new Object and assign to root

select (select id,`First Name` as Name) as t1, (select id,`Last Name` as LastName) as t2,MERGE_OBJECTS(t1,t2) as `$$ROOT`  from customers

Using with unwind with joins

select MERGE_OBJECTS((select t.CustomerID,t.Name),t.Rental) as `$$ROOT` from (select id as CustomerID,`First Name` as Name,unwind(Rentals) as Rental from customers) as t

PARSE_JSON(json string)

Group By and Having

Coming soon

Joins

Support INNER and OUTER Joins but does not unwind by default and the items are added as an array field.

There are several join hints to support automatically unwinding of joins:

• first
• last
• unwind

--return the first item in the array
select * from orders inner join `inventory|first` as inventory_docs on sku=item

--take the last item of the array
select * from orders inner join `inventory|last` as inventory_docs on sku=item

--unwind the array to multiple documents
select * from orders inner join `inventory|unwind` as inventory_docs on sku=item

Alternatively the explicit array functions can be used:

--return the first item in the array
select *,FIRST_IN_ARRAY(inventory) as inventory_docs from orders inner join `inventory` on sku=item

--take the last item of the array
select *,LAST_IN_ARRAY(inventory) as inventory_docs from orders inner join `inventory` on sku=item

--unwind the array to multiple documents
select *,UNWIND(inventory) as inventory_docs from orders inner join `inventory` on sku=item

NOTE: In sub select on where statement does not work as a join

--Won't Work (from MongoDB docs)
SELECT *, inventory_docs
FROM orders
WHERE inventory_docs IN (SELECT *
FROM inventory
WHERE sku= orders.item)

--use join instead
select *
from orders
inner join inventory inventory_docs
on sku=item

Sub Queries

Column Functions

Methods that perform operations on columns/fields

Comparison Operators

Aggregate Functions

SUM CASE

Sum Case logic is supported:

select sum(case when `Address.City`='Ueda' then 1 else 0 end) as Ueda,sum(case when `Address.City`='Tete' then 1 else 0 end) as Tete from `customers` group by `xxx`

Arrays

Methods that perform operations on array fields. Can be used as part of select statements and queries.

M-SQL uses sub-selects to query array fields in collections. E.g.

select (select * from Rentals where staffId=2) as t from `customers`

Using '$$ROOT' in sub select promotes the field to the root value of the array

select (select filmId as '$$ROOT' from Rentals where staffId=2) as t from `customers`

Slicing the array is supported by limit and offset in queries

select (select * from Rentals where staffId=2 limit 10 offset 5) as t from `customers`

NOTE: Sorting array in sub select is not supported and will need to use unwind

--Wont'Work
select id,(select * from Rentals order by id desc) as totalRentals from customers

NOTE: Aggregation functions not supported in sub select

--Wont'Work
select id,(select count(*) as count from Rentals) as totalRentals from customers

Unwind

Coming soon

Objects

Methods that perform operations on objects

$$ROOT

Specifying '$$ROOT' as a column alias sets the value to root object but only works with aggregates (unless contained in array sub select)

select t as `$$ROOT` from (select id,`First Name`,`Last Name`,lengthOfArray(Rentals,'id')  as numRentals from customers) as t

Mathematical Functions

Mathematical Operators

Conversion Functions

Literal types in where statements using conversion functions are converted automatically e.g.:

select to_date(date) as d from customers where to_date(date) > to_date('2012-01-01')

Will automatically convert the date value and result in the following:

{"$expr":{"$gt":[{"$toDate":"$date"},new Date("2012-01-01T00:00:00.000Z")]}}

Cast

Supports cast operations with the following type mappings:

select cast(1+`id` as varchar) as `id` from `customers`
select cast(abs(-1) as varchar) as `id` from `customers`
select cast('2021-01-01T00:00:00Z' as date) as `id` from `customers`

String Functions

Note: + (str + str) does not work for string concatenation.

Date Functions

Selecting on a calculated column by name

Calculated columns in where statements can only be used with aggregates

--have to repeat select statemnt as with sql rules
select id,Title,Rating,abs(id) as absId from `films` where abs(id)=1