unable to get JSON elements inside nested array using jsonpath syntax

Question

I am trying to get the departments id where location is USA in the following JSON

{
    "datapoints": [{
            "id": "default.1",
            "definedBy": "default/0.1",
            "featureValues": {
                "bui.displayname": "Health status",
                "bui.visibility": "normal",
                "default.access": "r",
                "default.basetype": "text",
                "default.description": "Aggregated health status",
                "default.format": "text/plain",
                "default.name": "health_status",
                "default.restriction": "re:(OK|WARN|ERROR|UNKNOWN)",
              "name" : [ 
                            { 
                                    "brand": "apple" ,
                                      "company": [
                                                  {
                                                 "location": "UAS"
                                                  }
                                            ],
                                     "manager": "stev"
                            } 
                         ]
            }
        }, {
            "id": "kdl.240",
            "definedBy": "kdl/0.9",
            "featureValues": {
                "bui.displayname": "Delta K",
                "bui.visibility": "normal",
                "default.access": "rw",
                "default.basetype": "real",
                "default.description": "Delta K",
                "default.name": "Delta_K",
                "default.privacy": "false",
                "default.restriction": "b32",
              "name" : [ 
                            { 
                                    "brand": "apple" ,
                                    "company": [
                                                {
                                                 "location": "canada"
                                                }
                                             ],
                                     "manager": "abc"
                            } 
                         ]
            }
        }
    ]
}

I tried

$.datapoints[?(@.featureValues.name[0].company[*].location == 'USA')].id

but this gives me not matched. I perform this test on https://jsonpath.com/

use `0` index for company too. `$.datapoints[?(@.featureValues.name[0].company[0].location == 'USA')].id` — Akshay G, Jul 13 '23 at 12:48

score 1 · Answer 1 · answered Jul 12 '23 at 13:03

I don't know if this is doable in a single request and I guess the results will be different between the implementation you are using (as you didn't specified any in your question)

However you can use the following query to look for the path(s) matching your criterias

$..[$.datapoints[?(@.featureValues)].featureValues.name[?(@.company)].company[?(@.location =='USA')]

For example in JS jp.nodes(obj, pathExpression[, count])

Once you got the path(s), you can get the matching node(s) and retrieve the id you are looking for.

Still in JS something you can use jp.nodes(obj, pathExpression[, count])

NB: data in your example, i guess there is a little typo as USA is written UAS

Hello, @Brice, i am unable to retrieve data using this path, tested on https://jsonpath.com/ — parlad, Jul 14 '23 at 13:56

score 0 · Answer 2 · answered Jul 12 '23 at 20:39

For completeness, with the IETF specification upcoming, the JSON Path that finds this for you takes advantage of nested paths inside the expression.

$.datapoints[?@.featureValues.name[?@.company[?@.location=='USA']]].id

Breaking this down

$.datapoints                      // find values in datapoints 
 [?                               // that have
   @.featureValues.name           // values in featureValues.name
     [?                           // that have
       @.company                  // values in company
         [?                       // that have
           @.location=='USA'      // location = 'USA'
         ]
     ]
 ]
.id                               // and get their id

This might be supported by some other online evaluators, but it's definitely supported by mine, https://json-everything.net/json-path, which already implements the pending specification.

unable to get JSON elements inside nested array using jsonpath syntax

2 Answers2

Linked