How to convert `json query` to `sql query`?

Question

I have an AngularJS App designed to build queries in JSON format, those queries are built with many tables, fields, and operators like "join", "inner", "where","and","or","like", etc.

AngularJS App is sending this JSON queries to my Django-Restframework backend, so I need to translate that JSON query into SQL query, to be able to run Raw SQL with previous validations of what tables/models are allowed for select.

I don't need a full JSON query to SQL query translation, I just want to translate selects with support for clauses like "where","and", "or", "group_by".

For better understanding of my question I put the following snippets:

{
  "selectedFields": {
    "orders": {
                "id": true,
                "orderdate": true},
    "customers": {
                  "customername": true,
                  "customerlastname": true}
  },
  "from": ["orders"],

  "inner_join":
    {
      "customers": {
        "on_eq": [
          {
            "orders": {
              "customderID": true
            },
          },
          {
            "customers": {
              "customerID": ture
            }
          }
        ]    
      }
    }
}

SELECT
    Orders.OrderID,
    Customers.CustomerName,
    Customers.CustomerLastName,
    Orders.OrderDate
FROM Orders
INNER JOIN Customers
ON Orders.CustomerID=Customers.CustomerID;

I took the example from: http://www.w3schools.com/sql/sql_join.asp

Please note that I am not trying to serialize any SQL query output to JSON.

Answer 1

I found a NodeJS package (https://www.npmjs.com/package/json-sql) who converts JSON queries to SQL queries, so I made a NodeJS script and then I create a class in Python to call NodeJS script.

With this approach I just need to send all AngularJS queries following this syntax (https://github.com/2do2go/json-sql/tree/master/docs#join)

NodeJS script.

// Use:
//$  nodejs reporter/services.js '{"type":"select","fields":["a","b"],"table":"table"}'
var jsonSql = require('json-sql')();

var json_query = JSON.parse(process.argv[2]);

var sql = jsonSql.build(json_query);

console.log(sql.query);

DRF class:

from unipath import Path
import json
from django.conf import settings
from Naked.toolshed.shell import muterun_js


full_path = str(Path(settings.BASE_DIR, "reporter/services.js"))


class JSONToSQL:

    def __init__(self, json_):
        self.json = json_
        self.sql = None
        self.dic = json.loads(json_)
        self.to_sql()

    def to_sql(self):
        response = muterun_js('%s \'%s\'' % (full_path, self.json))
        if response.exitcode == 0:
            self.sql = str(response.stdout).replace("\n","")