Extract data from a json string with a structure language

Question

I don't know if this thing exists or if a best practices already exists.

I have a json string and I would like to know if it's possible to extract data from this json using a query string like in sql.

Example with this json:

 [
 {
 "user": [
 {
 "id": 1,
 "name": "Smith",
 "visits": [
 {
 "id": 1,
 "date": "2016-08-30 16:00:00",
 "pageViews": 18
 }
 ]
 },
 {
 "id": 2,
 "name": "Willis",
 "visits": [
 {
 "id": 2,
 "date": "2016-08-30 18:00:00",
 "pageViews": 34
 }
 ]
 }
 ]
 }
]

If I execute a query like SELECT user.name WHERE user.visits.pageViews > 20 I get Willis

Else, what is the best way to do something like that in PHP ?

Thanks

• sjsam: Hmm, why not use jq? Also explain structure language?
• Mark Baker: array_filter() would be a good starting place

Answer 1

Use jq which is the right tool for the job. Suppose 39233103.json contains the sample input in the quesiton.

Sample Run

$ jq '.[]|.user[]|select(.visits[].pageViews>20)|.name' 39233103.json
"Willis"

To get the raw output use the -r option

$ jq -r '.[]|.user[]|select(.visits[].pageViews>20)|.name' 39233103.json
Willis

---

Now to do it from within php you could use [ exec ]. Suppose the below script is named 39233103.php

<?php
$output=array();
exec("jq -r '.[]|.user[]|select(.visits[].pageViews>20)|.name' 39233103.json", $output);
foreach ($output as $val){
echo $val."\n";
}
?>

Output

$ php 39233103.php
Willis

---

If you're not familiar with jq, [ this ] is a good starting point.