How to query JSON objects

Question

I'm working with a json file that has the following data structure.

{"students":[
    {"Name":"Wale", "state":"Lagos", "age":20, "hobby":"dancing"},
    {"Name":"Ebere", "state":"Enugu", "age":18, "hobby":"eating"},
    {"Name":"Musa", "state":"Kano", "age":24, "hobby":"swimming"}
]}

I'm working with the ionic framework and need to select all students from three variables say... state, age and hobby. If this was an sql statement it would look something like....

$query = "Select * from students WHERE state = 'Enugu' AND age = '20' AND hobby = 'swimming'".

So my question is, how can I do this in json?

Please update your post with the answer to the question @ode2k asked, plus a little more details. What language are you using to query the json data from? — dhrubo_moy, Sep 21 '16 at 23:46
Related question: [Is there a query language for JSON?](https://stackoverflow.com/q/777455/195835) — Simon East, Jun 14 '21 at 09:10

score 0 · Accepted Answer · answered Sep 21 '16 at 20:19

If you are looking for a special purpose language of its own, take a look at JSONiq, a query language specifically designed for JSON data model. Here's a simple example taken from Wikipedia:

for $p in collection("persons")
where $p.age gt 20
let $home := $p.phoneNumber[][$$.type eq "home"].number
group by $area := substring-before($home, " ")
return 
{
     "area code" : $area,
     "count" : count($p)
}

If you want functionality similar to SQL join clause, take a look at the example below:

let $stores :=
[
    { "store number" : 1, "state" : "MA" },
    { "store number" : 2, "state" : "MA" },
    { "store number" : 3, "state" : "CA" },
    { "store number" : 4, "state" : "CA" }
]
let $sales := [
     { "product" : "broiler", "store number" : 1, "quantity" : 20  },
     { "product" : "toaster", "store number" : 2, "quantity" : 100 },
     { "product" : "toaster", "store number" : 2, "quantity" : 50 },
     { "product" : "toaster", "store number" : 3, "quantity" : 50 },
     { "product" : "blender", "store number" : 3, "quantity" : 100 },
     { "product" : "blender", "store number" : 3, "quantity" : 150 },
     { "product" : "socks", "store number" : 1, "quantity" : 500 },
     { "product" : "socks", "store number" : 2, "quantity" : 10 },
     { "product" : "shirt", "store number" : 3, "quantity" : 10 }
]
let $join :=
    for $store in $stores[], $sale in $sales[]
    where $store."store number" = $sale."store number"
    return {
        "nb" : $store."store number",
        "state" : $store.state,
        "sold" : $sale.product
    }
return [$join]
-> [
    { "nb" : 1, "state" : "MA", "sold" : "broiler" },
    { "nb" : 1, "state" : "MA", "sold" : "socks" },
    { "nb" : 2, "state" : "MA", "sold" : "toaster" },
    { "nb" : 2, "state" : "MA", "sold" : "toaster" },
    { "nb" : 2, "state" : "MA", "sold" : "socks" },
    { "nb" : 3, "state" : "CA", "sold" : "toaster" },
    { "nb" : 3, "state" : "CA", "sold" : "blender" },
    { "nb" : 3, "state" : "CA", "sold" : "blender" },
    { "nb" : 3, "state" : "CA", "sold" : "shirt" }
]

score 0 · Answer 2 · answered Sep 21 '16 at 20:57

You could use .filter() to "query" your data set. For the specific case that you described, would be:

let data = {"students":[
    {"Name":"Wale", "state":"Lagos", "age":20, "hobby":"dancing"},
    {"Name":"Ebere", "state":"Enugu", "age":18, "hobby":"eating"},
    {"Name":"Musa", "state":"Kano", "age":24, "hobby":"swimming"}
]};

// ES6!!!
function filterStudents(students, name, age, state){
    return students.filter(student => student.state === state && student.age === age && student.Name === name)
}

console.log(filterStudents(data.students, 'Musa', 24, 'Kano'))

But i would suggest you to go with a library instead of creating your filtering cases manually. See if that lib (i'm the creator) will suit to your case:

https://github.com/DiegoZoracKy/data-query

How to query JSON objects

2 Answers2