Querying JSONPath using conditions on parallel node

My JSON has specific name values and id's associated with the name values in a parallel node (don't ask me why but I didn't design the JSON). I would like to retrieve name values based on a specific id. For example, I would like to retrieve the NAME - ABC Creations based on the ID - 50200df3-7476-4bab-86ab-1aae4c097095

I am using jsonpath-ng library for python

Full JSON:

{
   "Document":[
      {
         "Fields":[
            {
               "Type":{
                  "Text":"NAME",
                  "Confidence":97.65931701660156
               },
               "ValueDetection":{
                  "Text":"ABC Creations"
               },
               "PageNumber":1,
               "GroupProperties":[
                  {
                     "Types":[
                        "RECEIVER_SOLD_TO"
                     ],
                     "Id":"50200df3-7476-4bab-86ab-1aae4c097095"
                  }
               ]
            },
            {
               "Type":{
                  "Text":"NAME",
                  "Confidence":92.77919006347656
               },
               "ValueDetection":{
                  "Text":"Hardwood Industries, Inc."
               },
               "PageNumber":1,
               "GroupProperties":[
                  {
                     "Types":[
                        "VENDOR_REMIT_TO"
                     ],
                     "Id":"844e1bf2-6adb-43fc-af41-bb26844bffe6"
                  }
               ]
            },
            {
               "Type":{
                  "Text":"NAME",
                  "Confidence":93.58846282958984
               },
               "ValueDetection":{
                  "Text":"HARDWOOD INDUSTRIES, INC."
               },
               "PageNumber":1,
               "GroupProperties":[
                  {
                     "Types":[
                        "VENDOR"
                     ],
                     "Id":"1f7ec071-8c24-4160-979a-3c44d6884d55"
                  }
               ]
            }
         ]
      }
   ]
}

I am able to retrieve all the name values using :

$.Document[*].Fields[?(@.Type.Text=='NAME')].ValueDetection.Text

Output is:

[
  "ABC Creations",
  "Hardwood Industries, Inc.",
  "HARDWOOD INDUSTRIES, INC."
]

If I query based on the Id I don't get the name:

$.Document[*].Fields[*].GroupProperties[?(@.Id=='50200df3-7476-4bab-86ab-1aae4c097095')]

Output:

[
  {
    "Types": [
      "RECEIVER_SOLD_TO"
    ],
    "Id": "50200df3-7476-4bab-86ab-1aae4c097095"
  }
]

Any suggestions or help would be much appreciated.

Solution

You need to use nested queries.

$.Document[*]
   .Fields[?
     @.GroupProperties[?
       @.Id == '50200df3-7476-4bab-86ab-1aae4c097095'
     ]
   ]
 .ValueDetection.Text

(one-liner)

$.Document[*].Fields[[email protected][[email protected] == '50200df3-7476-4bab-86ab-1aae4c097095']].ValueDetection.Text

The first few segments $.Document[*] get you into the Fields array. Next, you want to start searching for items.

The outer query finds all of the objects with GroupProperties. Because this isn't being compared to anything, it forms an "existence test," meaning that if any values are returned, the node the query applies to is selected.

The inner query refines the outer query to only objects within the GroupProperties array which have the appropriate Id value.

Once you've identified the outer set, you can just select the values you want with .ValueDetection.Text.

Nested queries aren't universally supported, so you may need to check with the implementation you're using, however this does conform to the pending JSON Path spec.

You can play with this at https://json-everything.net/json-path (the playground for my .Net implementation).