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/

Aubade

use 0 index for company too. $.datapoints[?(@.featureValues.name[0].company[0].location == 'USA')].id – Peipeiffer

Your JSON has UAS as location value instead of USA – Peipeiffer

Answer 1

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

Rachellerachis

Hello, @Brice, i am unable to retrieve data using this path, tested on jsonpath.com – Aubade

Answer 2

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

$.datapoints[[email protected][[email protected][[email protected]=='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.

Duky