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
– Akshay G, Commented Jul 13, 2023 at 12:48

Brice · Accepted Answer · 2023-07-12 13:03:05Z

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

answered Jul 12, 2023 at 13:03

Brice

8145 silver badges16 bronze badges

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

1 Comment

parlad Over a year ago

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

gregsdennis · Accepted Answer · 2023-07-12 20:39:28Z

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.

Collectives™ on Stack Overflow

unable to get JSON elements inside nested array using jsonpath syntax

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related