JSONSql

Access JSON via sql like syntax. This is my real timesaver when I am building reporting SPA application.

install

$npm install json-sql-tool

Available method on select aggregation

min,max,avg,count,sum

Available operator on where

like, > , < ,>= ,<=

Having clause

having use alias on min,max,avg,count and operater availables are >, < ,>= ,<=

Usage

Sample json array dataset `

let data=[ {name:'A C' ,age:30,location:'London',dob:'1930-01-25',info:{married:'Y','total_child':2}}, {name:'A C' ,age:35,location:'London',dob:'1930-01-26',info:{married:'N','total_child':3}}, {name:'A B' ,age:36,location:'London',dob:'1930-01-27',info:{married:'Y','total_child':4}}, {name:'A A' ,age:15,location:'Paris',dob:'1998-08-23',info:{married:'Y','total_child':2}}, {name:'A B' ,age:16,location:'Paris',dob:'1998-08-23',info:{married:'Y','total_child':2}}, {name:'A C' ,age:17,location:'Kathmandu',dob:'2000-03-23',info:{married:'Y','total_child':2}}, {name:'A A' ,age:15,location:'Paris',dob:'1998-07-01',info:{married:'Y','total_child':2}}, {name:'A B' ,age:16,location:'Paris',dob:'1998-07-26',info:{married:'N','total_child':0}}, {name:'A C' ,age:17,location:'Paris',dob:'1998-07-20',info:{married:'N','total_child':3}}, {name:'A A' ,age:20,location:'Paris',dob:'1998-07-24',info:{married:'Y','total_child':2}}, {name:'B A' ,age:15,location:'Kathmandu',dob:'1998-08-23',info:{married:'Y','total_child':2}}, {name:'B A' ,age:16,location:'Kathmandu',dob:'1998-08-23',info:{married:'N','total_child':0}}, {name:'B C' ,age:15,location:'Kathmandu',dob:'1998-08-23',info:{married:'N','total_child':2}},

];


* Include the module

let JSONSql=require('json-sql-tool'); let objUtil=new JSONSql();



####  Query Sample select with where and order by 
On this sample we select the column and filter by using where and order by field [asc|desc]

let search_data=objUtil.select('name,location as ln,dob,info.married') .from(data) .where('info.married','=','Y') .where('location','like','london') .orderby('name asc') .fetch(); console.log(search_data);

You will have output of :

[ { name: 'A B', location: 'London', dob: '1930-01-27', info: { married: 'Y', total_child: 4 } }, { name: 'A C', location: 'London', dob: '1930-01-25', info: { married: 'Y', total_child: 2 } } ]

NOTE: Currently order by only detect integer and string. I am planning to order based date as well.

####  Query Sample 2 for aggregation (group by) and filter having
In this sample we have use count(),avg(),min(),max()  aggregation method.
Once we use aggregation method it will automatically group the result on selected field.

We have also used having filter on this sample. This will first create the result and then
 filter based on having filter. 

 and order the final result based on minimum age .

 NOTE: Currently order by only detect integer and string. I am planning to order based date as well.

search_data=objUtil.select('name,count(name) as cnt_name,avg(age) as avg_age,min(age),max(age)') .from(data) .having('cnt_name','>','2') .orderby('min_age desc') .fetch(); console.log(search_data);

You will have output of :

[ { name: 'A C', age: 30, cnt_name: 4, avg_age: 24.75, min_age: 17, max_age: 35 }, { name: 'A B', age: 36, cnt_name: 3, avg_age: 22.666666666666668, min_age: 16, max_age: 36 }, { name: 'A A', age: 15, cnt_name: 3, avg_age: 16.666666666666668, min_age: 15, max_age: 20 } ] `

NOTE: Currently order by only detect integer and string. I am planning to order based date as well.

Package detail

json-sql-tool

readme