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:

key	description	values	mandatory
type	the type of this level in the json	array, object, string, json	Yes
query	sql query of data from the db	string or array of strings with sql queries	No
fields	array of the fields of this elements (see fields headline below)	array of objects	No
keyField	(can appear only when type=object of the first nested field) A field from the query that its values will be used as keys of the object's values	name of a field from the query	No
refField	A field or fields from the query that its values will be used as reference for the query of the nested fields of this level	string or array of strings (DB column names)	No
dbName	name of the db column of this field	string	only for simple levels
name	name of the key in the output json that will contain this value	string	only for simple levels
nullable	if true, create this field even if the db value is null or empty	boolean	No
preLoadTables	(can apear only in the first level) an object that descripbes sql queries that will be executed in the begining of the sql-to-json process and could be accessed by memQuery queries. (see the example bellow)	object	No
memQuery	an sql query from the memory. the table names that can be used in this queries are the keys in the preLoadTables object (see the example bellow)	string or array of strings with sql queries	No
preDefinedkeys	(can appear only for type=object) an array of hard coded strings that will be used as keys of the object (instead of query result for example)	array of strings	No
diver	if true, set a nested object that the keys are the first [numOfKes] fields that apear in the query in this level.	boolean	No
numOfKeys	number of the first fields that apear in the query that should be keys of the nested object.	number	Yes - if diver=true
logLevel	if set to DEBUG, sql-to-json will print debug info for the current level. usefull for debugging	"DEBUG"	No

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": {}
  }
}