easyjsonsql
v1.0.12
Published
Access JSON via sql like syntax. Easy to use JSON datasets from SQL query.
Downloads
24
Maintainers
Keywords
Readme
JSONSql
Access JSON via sql like syntax. This is my real timesaver when I am building reporting SPA application.
install
$npm install easyjsonsql
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('easyjsonsql');
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.