sql-to-json v1.5.0

sql-to-json

Synopsis

This object is a generic tool to convert your sql tables to an any structured json object. the object gets a connected db client and a structure json object that describes the sql tables and the output json structure

Code Example

const SqlToJson = require('sql-to-json');
const mysql = require('mysql'); // can be any sql db client with query method.

const mySqlDbClient = mysql.createConnection({credentials object});
mySqlDbClient.connect();

const struct = {};

const instance = new SqlToJson(dbClient);
yield* instance.executeGen(struct);

mySqlDbClient.end();

structure file

The structure file is a json file that describes the sql tables and the output json structure. the structure is an hierarchy object that each of its levels describes a level in the output json. each level can be: a simple level - descbribes a simple value in the json and a nested level that describes a list (object or array) of many simple or nested levels inside. a special level always have a "fields" key that contains an array of objects that describe the nested levels.

keys description:

fields

The fields array in the stucture json is used to describe the next level in the output json. fields should appear only when type is list (object or array).

query

In the query you can use any sql that will be legal by your DB. you can use paramter bindings that will be filled by the reference filed or fields of the last level that has refField key.

• note: it is much better to use memQuery and not query for preventing too many requests to your db and then getting better performances.

memQuery

In the memQuery you can use any sql that legal by npm alasql library. you can use paramter bindings that will be filled by the reference filed or fields of the last level that has refField key.

example

• look in examples folder For more structure files exampales.

structure file

{
    "type": "object",
    "preLoadTables": {
        "direct_publishers": "select * from publishers",
        "cost_models": "select * from costModels",
        "cost_models_countries": "select * from cm_countries",
        "cost_model_types": "select * from cm_types",
        "activities": "select * from activities",
        "countries": "select * from countries"
    },
    "memQuery": "select id from direct_publishers",
    "keyField": "id",
    "refField": "id",
    "fields": [{
        "type": "object",
        "memQuery": "select dp.name, ac.name as activity_name from direct_publishers dp inner join activities ac on dp.activity_id = ac.id where dp.id = ?",
        "fields": [{
            "dbName": "name",
            "name": "name",
            "type": "string"
        }, {
            "dbName": "activity_name",
            "name": "activity",
            "type": "string"
        }, {
            "name": "costModels",
            "type": "object",
            "nullable": true,
            "memQuery": "select id, domain from cost_models where publisher_id = ?",
            "keyField": "domain",
            "refField": "id",
            "fields": [{
                "type": "object",
                "memQuery": "select c.country_code from cost_models cm inner join cost_models_countries cmc on cm.id = cmc.cost_model_id inner join countries c on cmc.country_id = c.id where cm.id = ?",
                "keyField": "country_code",
                "fields": [{
                    "type": "object",
                    "memQuery": [
                        "select c.country_code from cost_models cm inner join cost_models_countries cmc on cm.id =      
                                cmc.cost_model_id inner join countries c on cmc.country_id = c.id where cm.id = ?",
                        "select 'all' as country_code"
                    ],
                    "fields": [{
                        "dbName": "type_name",
                        "name": "type",
                        "type": "string"
                    }, {
                        "dbName": "value",
                        "name": "value",
                        "type": "number"
                    }]
                }]
            }]
        }]
    }]
}

output

{
  "1": {
    "name": "test",
    "activity": "activity1",
    "costModels": {
      "abc": {
        "AD": {
          "type": "cpc",
          "value": 2.2
        },
        "US": {
          "type": "cpi",
          "value": 2.2
        }
      },
      "www.cnn.com": {
        "IL": {
          "type": "cpi",
          "value": 2.2
        },
        "US": {
          "type": "cpm",
          "value": 2.2
        }
      }
    }
  },
  "2": {
    "name": "test",
    "activity": "activity2",
    "costModels": {}
  }
}