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

Hmm, why not use jq? Also explain structure language?

sjsam
– sjsam

2016-08-30 16:56:31 +00:00
Commented Aug 30, 2016 at 16:56 — sjsam
– sjsam, Commented Aug 30, 2016 at 16:56
array_filter() would be a good starting place

Mark Baker
– Mark Baker

2016-08-30 16:59:03 +00:00
Commented Aug 30, 2016 at 16:59 — Mark Baker
– Mark Baker, Commented Aug 30, 2016 at 16:59

sjsam · Accepted Answer · 2016-08-30 17:31:55Z

0

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.

edited Aug 30, 2016 at 17:31

answered Aug 30, 2016 at 17:15

sjsam

22k6 gold badges63 silver badges114 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Extract data from a json string with a structure language

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related