JSON is a format to sharing data as alternative to XML because this is very ligtweight and very readable. JSON mostly used in JavaScript Programming but infact this can be use for another programming language including PHP, Phyton, Ruby, Java, etc.

Using JSON is very simple, because JavaScript can read directly into the object. But the problem is to query the JSON is not easy when it is on big array. So in this topic we will try to how to make JSON doing query to search, join, merge, groupBy, sum, groupDetail, orderBy, limit, skip, pagination, etc.

JSON Query

To JSON Query I will try to use as native way and using library. So what I’m gonna choose is fly-json-odm. Why? Because it is very fast and lightweight, the size is only 4Kb when minified and gzipped. Also this library is build with the native way, so it is fair enough to compare with the native way.

Example Data

I will use this example data for this tutorial

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
var user = [
{ user_id:1, name:'John', age:13 },
{ user_id:5, name:'Wilfred', age:26 },
{ user_id:3, name:'Merry', age:31 }
];

var user_contact = [
{ id:1, address:'California', email:'[email protected]', phone:'44556677' },
{ id:2, address:'South West', email:'[email protected]', phone:'12322144' },
{ id:3, address:'Jakarta', email:'[email protected]', phone:'89119796' },
{ id:4, address:'Sidney, Australia', email:'[email protected]', phone:'33525478' },
{ id:5, address:'Tokyo', email:'[email protected]', phone:'51167889' }
];

var car_brand = [
{ brand:'Ford', color:'black', stock:12 },
{ brand:'Ford', color:'white', stock:26 },
{ brand:'Ferarri', color:'red', stock:8 },
{ brand:'Porsche', color:'white', stock:9 },
{ brand:'Peugot', color:'white', stock:13 }
];

Using Fly Json ODM

Because I choose to use fly-json-odm library to compare with native way, this is how to use fly-json-odm:

  • You can install from NPM for NodeJS

    1
    $ npm install fly-json-odm
    1
    2
    3
    const FlyJson = require('fly-json-odm');
    var odm = new FlyJson();
    // below here is for your query
  • You can use CDN for browser

    1
    2
    3
    4
    5
    <script src="https://cdn.jsdelivr.net/npm/[email protected]/dist/flyjson.min.js"></script>
    <script>
    var odm = new FlyJson();
    // below here is for your query
    </script>

    For more detail is on their documentation.

Tutorial JSON Query

By using native way below here you should consider about:

  1. Clone array to prevent your original data.
  2. Loop is blocking for big data.
  3. This is just the basic function you need make some improve for using in production.

So let’s start the tutorial json query with common cases.

Simple searching by key value in json array.

  • Native way
    To perform search in JSON you will need filter and indexOf function like this below.

    1
    2
    3
    4
    5
    6
    7
    8
    function search(obj1,name,searchTerm) {
    return obj1.filter(function(key) {
    return key[name].indexOf(searchTerm) > -1;
    });
    }

    var result = search(user,'name','Wil');
    console.log(result);
  • Fly Json ODM way

    1
    2
    var result = odm.set(user).where('name','==','Wil').exec();
    console.log(result);

    With fly-json-odm you are able to search by using comparison operator.
    Example: ==, >, <=, LIKE, NOT LIKE, etc.

  • Expected result
    You can see the at jsfiddle.

    1
    [{"user_id":5,"name":"Wilfred","age":26}]

Join On

Join on means joining two json array by key and result as nested.

  • Native way

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    36
    37
    38
    39
    40
    function join(obj1,obj2,alias,name1,name2) {

    // we create index for obj2, to be use later in loop
    const indexB = obj2.reduce((result,item) => {
    result[item[name2]] = item;
    return result;
    }, {});

    // create result in array first
    var result = [];

    // mapping the obj1
    obj1.map(function(value,index){
    // create newdata
    var newdata = {};
    // get the obj1 key to array
    var arr = Object.keys(obj1[index]);
    // count the array
    var l = arr.length;
    // loop the obj1
    for(var i=0;i<l;i++) {
    // using alias will add new object nested for obj2
    if(arr[i] === name1) {
    if(alias === arr[i]) {
    newdata[arr[i]] = indexB[obj1[index][arr[i]]];
    } else {
    newdata[alias] = indexB[obj1[index][arr[i]]];
    newdata[arr[i]] = value[arr[i]];
    }
    } else {
    newdata[arr[i]] = value[arr[i]];
    }
    }
    // push new object into result
    result.push(newdata);
    });
    return result;
    }
    var result = join(user,user_contact,'user_id','user_id','id');
    console.log(result);

    Join two json array as nested actualy is very complicated in native way.

  • Fly Json ODM way

    1
    2
    var result = odm.set(user).join('user_id',user_contact).on('user_id','id').exec();
    console.log(result);

    With fly-json-odm is very simple and your code will be more cleaner.

  • Expected result
    You can see at jsfiddle

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    [{
    "user_id": {
    "id": 1,
    "address": "California",
    "email": "[email protected]",
    "phone": "44556677"
    },
    "name": "John",
    "age": 13
    }, {
    "user_id": {
    "id": 5,
    "address": "Tokyo",
    "email": "[email protected]",
    "phone": "51167889"
    },
    "name": "Wilfred",
    "age": 26
    }, {
    "user_id": {
    "id": 3,
    "address": "Jakarta",
    "email": "[email protected]",
    "phone": "89119796"
    },
    "name": "Merry",
    "age": 31
    }]

Join Merge

Join Merge means joining two json array without nested.

  • Native way

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    function joinMerge(obj1,obj2,name1,name2) {
    // we create index for obj2, to be use later in map
    const indexB = obj2.reduce((result,item) => {
    result[item[name2]] = item;
    return result;
    }, {});
    return obj1.map(item => Object.assign(item,indexB[item[name1]]));
    }
    var result = joinMerge(user,user_contact,'user_id','id');
    console.log(result);
  • Fly Json ODM way

    1
    2
    var result = odm.set(user).join('user_id',user_contact).merge('user_id','id').exec();
    console.log(result);
  • Expected result
    You can see at jsfiddle.

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    [{
    "user_id": 1,
    "name": "John",
    "age": 13,
    "id": 1,
    "address": "California",
    "email": "[email protected]",
    "phone": "44556677"
    }, {
    "user_id": 5,
    "name": "Wilfred",
    "age": 26,
    "id": 5,
    "address": "Tokyo",
    "email": "[email protected]",
    "phone": "51167889"
    }, {
    "user_id": 3,
    "name": "Merry",
    "age": 31,
    "id": 3,
    "address": "Jakarta",
    "email": "[email protected]",
    "phone": "89119796"
    }]

Group By + Sum

This is just grouping by name property of json array. Support with SUM but this is optional.

  • Native way

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    function groupBy(obj1, name, sumField=[]) {
    var l = sumField.length;
    var data = obj1.reduce(function(res, obj) {
    obj.item_count = 1;
    if (!(obj[name] in res)) {
    res.__array.push(res[obj[name]] = obj);
    } else {
    for(var i=0;i<l;i++) {
    res[obj[name]][sumField[i]] += obj[sumField[i]];
    }
    res[obj[name]]['item_count'] += 1;
    }
    // average
    for(var i=0;i<l;i++) {
    res[obj[name]]['average_'+sumField[i]] = (res[obj[name]][sumField[i]]/res[obj[name]]['item_count']);
    }
    return res;
    }, {__array:[]});
    return data.__array;
    }
    var result = groupBy(car_brand, 'brand', ['stock']);
    console.log(result);
  • Fly Json ODM way

    1
    2
    var result = odm.set(car_brand).groupBy('brand',['stock']).exec();
    console.log(result);
  • Expected result
    You can see at jsfiddle.

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    [{
    "brand": "Ford",
    "color": "black",
    "stock": 38,
    "item_count": 2,
    "average_stock": 19
    }, {
    "brand": "Ferarri",
    "color": "red",
    "stock": 8,
    "item_count": 1,
    "average_stock": 8
    }, {
    "brand": "Porsche",
    "color": "white",
    "stock": 9,
    "item_count": 1,
    "average_stock": 9
    }, {
    "brand": "Peugot",
    "color": "white",
    "stock": 13,
    "item_count": 1,
    "average_stock": 13
    }]

Grouping Detail

This will grouping the json array and the detail will display as nested.

  • Native way

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    function groupDetail(obj1, name, groupName='') {
    var data = obj1.reduce((objectsByKeyValue, obj) => {
    const value = obj[name];
    objectsByKeyValue[value] = (objectsByKeyValue[value] || []).concat(obj);
    return objectsByKeyValue;
    }, {});
    var group = [];
    if(groupName) {
    group.push({[groupName]:data});
    } else {
    group.push({[name]:data});
    }
    return group;
    }
    var result = groupDetail(car_brand, 'brand');
    console.log(result);
  • Fly Json ODM way

    1
    2
    var result = odm.set(car_brand).groupDetail('brand').exec();
    console.log(result);
  • Expected result
    You can see at jsfiddle.

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    [{
    "brand": {
    "Ford": [{
    "brand": "Ford",
    "color": "black",
    "stock": 12
    }, {
    "brand": "Ford",
    "color": "white",
    "stock": 26
    }],
    "Ferarri": [{
    "brand": "Ferarri",
    "color": "red",
    "stock": 8
    }],
    "Porsche": [{
    "brand": "Porsche",
    "color": "white",
    "stock": 9
    }],
    "Peugot": [{
    "brand": "Peugot",
    "color": "white",
    "stock": 13
    }]
    }
    }]

Order By

To sort the json array in ASC or DESC.

  • Native way

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    function _sortBy(field, reverse, primer) {
    var key = primer ?
    function(x) {return primer(x[field])} :
    function(x) {return x[field]};
    reverse = !reverse ? 1 : -1;
    return function (a, b) {
    return a = key(a), b = key(b), reverse * ((a > b) - (b > a));
    }
    }

    function orderBy(obj1,name,desc=true,primer) {
    return obj1.sort(_sortBy(name,desc,primer));
    }

    var result = orderBy(user,'user_id');
    console.log(result);
  • Fly Json ODM way

    1
    2
    var result = odm.set(user).orderBy('user_id').exec();
    console.log(result);
  • Expected result
    You can see at jsfiddle.

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    [{
    "user_id": 5,
    "name": "Wilfred",
    "age": 26
    }, {
    "user_id": 3,
    "name": "Merry",
    "age": 31
    }, {
    "user_id": 1,
    "name": "John",
    "age": 13
    }]

Limit

Limit the json array output to display.

  • Native way

    1
    2
    3
    4
    5
    6
    7
    function limit(obj1, size) {
    obj1.length = size;
    return obj1;
    }

    var result = limit(user,2);
    console.log(result);
  • Fly Json ODM way

    1
    2
    var result = odm.set(user).take(2).exec();
    console.log(result);
  • Expected result
    You can see at jsfiddle.

    1
    [{"user_id":1,"name":"John","age":13},{"user_id":5,"name":"Wilfred","age":26}]

Skip

Skip some element of json array output.

  • Native way

    1
    2
    3
    4
    5
    6
    function skip(obj1, size) {
    return obj1.slice(size);
    }

    var result = skip(user,1);
    console.log(result);
  • Fly Json ODM way

    1
    2
    var result = odm.set(user).skip(1).exec();
    console.log(result);
  • Expected result
    You can see at jsfiddle.

    1
    2
    3
    4
    5
    6
    7
    8
    9
    [{
    "user_id": 5,
    "name": "Wilfred",
    "age": 26
    }, {
    "user_id": 3,
    "name": "Merry",
    "age": 31
    }]

Pagination

When data is big, we should don’t display them all, so we must create a pagination.
Actualy you can just doing this by combining limit and skip, but this is the alternative method.

  • Native way

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    function paginate(obj1, page, page_size) {
    var count = obj1.length;
    --page; // because pages logically start with 1, but technically with 0
    obj1 = obj1.slice(page * page_size, (page + 1) * page_size);
    return {
    result:obj1,
    metadata: {
    page:(page+1),
    page_size:page_size,
    total_page:Math.ceil(count / page_size),
    total_records:count
    }
    }
    }

    var result = paginate(user_contact,1,2);
    console.log(result);
  • Fly Json ODM way

    1
    2
    var result = odm.set(user_contact).paginate(1,2);
    console.log(result);
  • Expected result
    You can see at jsfiddle.

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    {
    "result": [{
    "id": 1,
    "address": "California",
    "email": "[email protected]",
    "phone": "44556677"
    }, {
    "id": 2,
    "address": "South West",
    "email": "[email protected]",
    "phone": "12322144"
    }],
    "metadata": {
    "page": 1,
    "page_size": 2,
    "total_page": 3,
    "total_records": 5
    }
    }

Conclusion

Native way is very fast and no one libraries can beat the native way. But for complex json query, your code will hard to read and you will lose many hours of your time just only to create the function for spesific condition.

With using fly-json-odm, to do json query is more easier because:

  • Similar as SQL Query.
  • Function is chainable.
  • Asynchronous support.
  • Work in memory so your original data is safe.

There is lodash, underscore and ramda which is also help you to json query. But I don’t use it because for me it is too bloated and slow in performance to use in browser. If you looking for tutorial json query with this library, you can easily find through google because too many article write this out there.

Thank you