Filter on children level, return the parent node #287

SpikeBlues · 2016-12-05T00:33:46Z

I wonder if it is possible to filter on the child level complex objects (not simple string), and retrieve those parent nodes whose children matches the predicts.

Taking the bookstore example from Jayway's github project: https://github.com/jayway/JsonPath#path-examples.

If I would change "author" to a more complex object like following:

  "authors": [
    {
      "firstName": "Nigel",
      "lastName": "Rees"
    },
    {
      "firstName": "Evelyn",
      "lastName": "Waugh"
    }
  ]

With that, how can I get back "all books that were written by someone with last name of 'Waugh' " ?

I have tried with Json Evaluator (http://jsonpath.herokuapp.com/) to use something like $.store.book[?(@.authors[?(@.lastName == 'Waugh')])] , but it seems like the filter doesn't work. I am guessing Jayway does not allow a nested predicates. If that's the case, would there be any workaround for this?

The text was updated successfully, but these errors were encountered:

tjakopanec · 2016-12-07T15:35:20Z

Works fine for me, though your jsonpath has some errors.
Change author to authors and just use just the last name in your jsonpath.

This one $.store.book[?(@.authors[?(@.lastName == 'Waugh')])] returns expected result.

SpikeBlues · 2016-12-13T07:37:37Z

Hi @tjakopanec Thanks for pointing it out. I have fixed my typo. However, I don't see it working as my expectation. The filter does not work as it will return everything including those books whose authors' name were not "Waugh". Would you give it a try?

tjakopanec · 2016-12-13T10:34:58Z

I've just tried it again and it works as I expect it to run. Just to avoid any confusion, this is my input:
{ "store": { "book": [ { "category": "reference", "author": "Nigel Rees", "title": "Sayings of the Century", "price": 8.95 }, { "category": "fiction", "authors": [ { "firstName": "Nigel", "lastName": "Rees" }, { "firstName": "Evelyn", "lastName": "Waugh" } ], "title": "Sword of Honour", "price": 12.99 }, { "category": "fiction", "author": "Herman Melville", "title": "Moby Dick", "isbn": "0-553-21311-3", "price": 8.99 }, { "category": "fiction", "author": "J. R. R. Tolkien", "title": "The Lord of the Rings", "isbn": "0-395-19395-8", "price": 22.99 } ], "bicycle": { "color": "red", "price": 19.95 } }, "expensive": 10 }

This is the jsonPath I use: $.store.book[?(@.authors[?(@.lastName == 'Waugh')])]

This is the result I'm getting: [ { "category" : "fiction", "authors" : [ { "firstName" : "Nigel", "lastName" : "Rees" }, { "firstName" : "Evelyn", "lastName" : "Waugh" } ], "title" : "Sword of Honour", "price" : 12.99 } ]

Is this the result you would expect?

SpikeBlues · 2016-12-14T00:53:46Z

Hi @tjakopanec thanks for the updates.
It works with your particular Json input, as the second element in the book array is the only one with a 'complicated' author object. However in my case if all books have a child arraynode authors, the filtering logics does not work as intended. Would you please have a look at my example as follows?

  "store": {
    "book": [
      {
        "category": "reference",
        "authors": [
          {
            "firstName": "Nigel",
            "lastName": "Rees"
          },
          {
            "firstName": "Evelyn",
            "lastName": "Waugh"
          }
        ],
        "title": "Sayings of the Century",
        "price": 8.95
      },
      {
        "category": "fiction",
        "authors": [
          {
            "firstName": "A",
            "lastName": "B"
          },
          {
            "firstName": "C",
            "lastName": "D"
          }
        ],
        "title": "Sword of Honour",
        "price": 12.99
      },
      {
        "category": "fiction",
        "authors": [
          {
            "firstName": "A",
            "lastName": "D"
          },
          {
            "firstName": "Evelyn",
            "lastName": "X"
          }
        ],
        "title": "Moby Dick",
        "isbn": "0-553-21311-3",
        "price": 8.99
      },
      {
        "category": "fiction",
        "authors": [
          {
            "firstName": "Nigel",
            "lastName": "Rees"
          },
          {
            "firstName": "Evelyn",
            "lastName": "X"
          }
        ],
        "title": "The Lord of the Rings",
        "isbn": "0-395-19395-8",
        "price": 22.99
      }
    ],
    "bicycle": {
      "color": "red",
      "price": 19.95
    }
  },
  "expensive": 10
}

The JsonPath I am using is the same as yours: $.store.book[?(@.authors[?(@.lastName == 'Waugh')])]

Thank you.

tjakopanec · 2016-12-19T09:03:25Z

Sorry for the late reply. Yeah, you are right. I've tried it on some of my examples and it's not working correctly.

I guess the author of the library should have a look then.

geewerx · 2017-02-27T13:42:53Z

+1 Seeing the same issue. This is stopping us us writing any jsonpath queries containing nested filters.

gs-skant · 2017-08-17T11:39:56Z

Any update on how to fetch parent when filter is applied on child node

tilleti · 2017-11-30T22:11:08Z

Hi, has there been any update on this issue?
If not Jayway's, do any other Jsonpath implementations support this feature (grabbing the parent based on a predicate on a child)?

akshayamaldhure · 2017-12-07T04:47:33Z

Really looking forward to have this issue resolved for the Jayway implementation.

which is not supported by the current version of jayway json-path refer json-path/JsonPath#287

guranya · 2018-08-28T18:31:18Z

This functionality is needed in our project. Any updates?

pavithrakamath · 2018-09-05T07:26:57Z

really needed feature to extract parent data from filtering nested arrays

BrandonDudek · 2018-09-14T15:44:00Z

+1

pavelgordon · 2018-09-17T14:02:24Z

+1

oprince · 2018-09-27T14:29:45Z

+1

ravimandli · 2018-09-27T19:59:03Z

+1

lamp12 · 2018-10-05T10:19:16Z

+1

igetsman · 2018-10-05T11:12:49Z

+1

rafaelaazevedo · 2018-10-10T21:09:41Z

+1

rafaelaazevedo · 2018-10-10T22:49:15Z

just found a workaround, now I am using the library "jsonpath-plus": "^0.16.0" and I am able to search and go back to the parent node using the expression: $..[?(@.firstName=="A")]^title

tristanbrandt014 · 2018-10-12T10:34:44Z

+1

geewerx · 2018-10-12T10:41:16Z

Project appears to be dormant...

paragguruji · 2018-10-12T22:48:43Z

+1

Ordiel · 2018-10-13T13:26:04Z

+1

vtkstef · 2018-11-08T21:55:04Z

+1

happynev · 2018-11-13T14:12:00Z

+1

MaksSieve · 2018-12-03T13:25:21Z

+1

sanjana-bhat · 2019-01-15T21:54:00Z

This works for me if authors was a map and not a list $.store.book[?('Waugh' == @['authors']['lastName'])]. This works as well $.store.book[?('Waugh' == @['authors'][1]['lastName'])] but it doesn't seem to like using wildcard in expressions

ersione · 2019-01-22T15:10:26Z

$.store[?(@.friends[?(@.name == 'Shawn Holman')])]
I hope to it will works fine.

ghost · 2020-03-30T21:33:36Z

Is there any any update on this one, this would be really helpful.

codermrrob · 2020-06-10T06:38:38Z

I found this works as expected with Json.Net, at least with version 12.0.3

My query $.purchases[?(@.payments[?(@.type == 'CASH')])]

I can't post the data here without a lot of heavy editing but the query is the same nested array query to return the parent purchase objects where there is a payment with type CASH.

andrew-property-xyz · 2020-06-10T16:54:00Z

Having same issue but perhaps this behaviour is as per jsonpath spec. If authors is an array, the parent node of a filter for, say, lastname=="Waugh" is simply the authors array (and not the book object to which the array belongs).

The jsonpath-plus module behaves the same. So, we either need support for nested filters or an option to get the parent of the parent (i.e. the parent of the array which will be the book object I'm looking for), or to ignore/skip arrays when getting the parent.

Currently, to get the desired result, I have to first do a paths request and then a value request using the object index extracted from the paths result. So, two jsonpath evaluations to find a book (or books) that have an author with a lastname of "Waugh".

serhatdirihan · 2020-11-22T10:08:54Z

As a workaround (it works on https://jsonpath.herokuapp.com/ but not other simulators):

JsonPath : $.[?(@.c[*].d contains "d3" )]['a', 'b']

Input :
[
{
"a": "a1",
"b": "b1",
"c": [
{
"d": "d1",
"e": "e1"
},
{
"d": "d2",
"e": "e2"
}
]
},
{
"a": "a2",
"b": "b2",
"c": [
{
"d": "d3",
"e": "e3"
}
]
}
]

It brings the correct result :

[
{
"a" : "a2",
"b" : "b2"
}
]

kean-logan · 2020-12-09T14:18:37Z

Building off of the previous answer from ArtVandalayy - using the modified book store example input (with authors as a complex object):

{
	"store": {
		"book": [
			{
				"category": "reference",
				"authors": [
					{
						"firstName": "Nigel",
						"lastName": "Rees"
					},
					{
						"firstName": "Evelyn",
						"lastName": "Waugh"
					}
				],
				"title": "Sayings of the Century",
				"price": 8.95
			},
			{
				"category": "fiction",
				"authors": [
					{
						"firstName": "Herman",
						"lastName": "Melville"
					},
					{
						"firstName": "Somebody",
						"lastName": "Else"
					}
				],
				"title": "Moby Dick",
				"isbn": "0-553-21311-3",
				"price": 8.99
			},
			{
				"category": "fiction",
				"authors": [
					{
						"firstName": "Evelyn",
						"lastName": "Waugh"
					},
					{
						"firstName": "Nigel",
						"lastName": "Rees"
					}
				],
				"title": "Sword of Honour",
				"price": 12.99
			},
			{
				"category": "fiction",
				"authors": [
					{
						"firstName": "J. R. R.",
						"lastName": "Tolkien"
					}
				],
				"title": "The Lord of the Rings",
				"isbn": "0-395-19395-8",
				"price": 22.99
			}
		],
		"bicycle": {
			"color": "red",
			"price": 19.95
		}
	},
	"expensive": 10
}

Using this path, which filters on authors with last name given:
$.store.book[?(@.authors..lastName contains "Waugh")].title

Or alternate path if deepscan operator '..' is not supported:
$.store.book[?(@.authors[*].lastName contains "Waugh")].title

We get this correct output according to https://jsonpath.herokuapp.com/ ver 2.3.0 - 2017-07-04 14:54:00

[
   "Sayings of the Century",
   "Sword of Honour"
]

rasheedzrt · 2021-02-25T15:15:08Z

Hello Everyone,

I have a similar requirement.

{
"store" : {
"10162021" : {
"id" : 812340,
"properties" : {
"server" : "server1.example.org",
"serverip" : "",
}
},
"10162022" : {
"properties" : {
"serverip" : "127.0.0.1",
"server" : "server2.example.org",
},
"id" : 8890890
}
}
}

where I need the value of the entries right under sore like these values "10162022" "10162021" from above code.

appreciate any suggestions.

tolpp · 2021-06-01T12:22:47Z

It's may not be a bug. @ArtVandalayy makes a good point. As stated in documentation, filter expression must evaluate to a boolean value.

The problem with previous solution $.store.book[?(@.authors[?(@.lastName == 'Waugh')])] is;

The nested expression ?(@.lastName == 'Waugh') evaluates to a boolean, which is OK,
But, main expression ?(@.authors[XX]) emits an object. So we can assume that, $.store.book[?(@.authors[*])] produces the same result as expression $.store.book[?(@.authors[?(@.lastName == 'Waugh')])] when there is at least one author with lastName 'Waugh'.

@ArtVandalayy's solution converts main expression to the boolean;

$.[?(@.c[*].d contains "d3" )]['a', 'b']

If we convert it for the example, it will be something like

$.store.book[?(@.authors[?(@.lastName == 'Waugh')].lastName contains 'Waugh')]

Yeah, it works, but there are other options.

Better solution

We can check for an empty.

$.store.book[?(@.authors[?(@.lastName == 'Waugh')] empty false)]

When filter expression checks for an empty false, then result contains only the books with an author that last name is 'Waugh'. When empty true, the result will become the inverted version of the first one. The books of authors with lastName 'Waugh' will become excluded from the list.

And, there are more filter operators to use: https://github.com/json-path/JsonPath#filter-operators

raviraj-bhalerao · 2023-08-07T14:44:09Z

@tolpp, @dengmingcong, @thadguidry, @VerkhovtsovPavel

I tried
$.store.book[?(@.authors[?(@.lastName == 'Waugh')] empty false)]
and
$.store.book[?(@.authors[?(@.lastName == 'Waugh')].lastName contains 'Waugh')]
with jsonPath.Com and newtonsoft 13.0.3.

It does not work.

JsonPath fails as - "jsonPath: Unexpected token '?': _$v.authors[?($_v.lastName == 'Waugh'"

NewtonSoft fails as - "'Could not read query operator.'"

Am I missing anything?

tolpp · 2023-08-07T15:10:31Z

I tried $.store.book[?(@.authors[?(@.lastName == 'Waugh')] empty false)] and $.store.book[?(@.authors[?(@.lastName == 'Waugh')].lastName contains 'Waugh')] with jsonPath.Com and newtonsoft 13.0.3.

It does not work.

JsonPath fails as - "jsonPath: Unexpected token '?': _$v.authors[?($_v.lastName == 'Waugh'"

NewtonSoft fails as - "'Could not read query operator.'"

Am I missing anything?

I think jsonpath[.]com is based on different JsonPath implementation.(https://github.com/ashphy/jsonpath-online-evaluator)

It still works on https[:/]/www.javainuse[.]com/jsonpath

@raviraj-bhalerao

raviraj-bhalerao · 2023-08-07T15:14:31Z

@tolpp
That was very quick ans. And thank you very much.
True it works with javainuse.

Would you have any idea where I can see how nested queries can be written for newtonsoft or how this query should be changed to get same results in newtonsoft please?

kallestenflo added the BUG label Feb 27, 2017

ptrthomas added a commit to karatelabs/karate that referenced this issue Feb 10, 2018

example for how to grab parent by nested json-path filter

2877fb0

which is not supported by the current version of jayway json-path refer json-path/JsonPath#287

hf-kklein mentioned this issue Feb 12, 2019

Getting parent node #522

Open

hf-kklein mentioned this issue Jul 29, 2019

JSON Path is taking duplicate values #559

Open

hf-kklein mentioned this issue May 28, 2020

Get object from two dimensional array with selection #617

Open

jcallaghan mentioned this issue Jun 16, 2020

Upgrade available for Home Assistant version sensor and DevOps automation 🛠😎🚀 jcallaghan/home-assistant-config#105

Open

6 tasks

thadguidry mentioned this issue Aug 29, 2020

Add GREL 'getFields' function to know what keys are available in a JSON object OpenRefine/OpenRefine#2186

Open

zengmmm00 mentioned this issue May 20, 2021

Fix Issue394: get parent node by child node #713

Open

thadguidry mentioned this issue Jul 9, 2021

Add JSONPath Plus wrapper as a GREL function OpenRefine/OpenRefine#2515

Open

mattmart3 mentioned this issue Oct 28, 2021

Nested filters RedisJSON/RedisJSON#494

Open

Filter on children level, return the parent node #287

Filter on children level, return the parent node #287

Comments

SpikeBlues commented Dec 5, 2016 • edited Loading

tjakopanec commented Dec 7, 2016

SpikeBlues commented Dec 13, 2016

tjakopanec commented Dec 13, 2016

SpikeBlues commented Dec 14, 2016

tjakopanec commented Dec 19, 2016

geewerx commented Feb 27, 2017

gs-skant commented Aug 17, 2017

tilleti commented Nov 30, 2017

akshayamaldhure commented Dec 7, 2017

guranya commented Aug 28, 2018

pavithrakamath commented Sep 5, 2018

BrandonDudek commented Sep 14, 2018

pavelgordon commented Sep 17, 2018

oprince commented Sep 27, 2018

ravimandli commented Sep 27, 2018

lamp12 commented Oct 5, 2018

igetsman commented Oct 5, 2018

rafaelaazevedo commented Oct 10, 2018

rafaelaazevedo commented Oct 10, 2018 • edited Loading

tristanbrandt014 commented Oct 12, 2018

geewerx commented Oct 12, 2018

paragguruji commented Oct 12, 2018

Ordiel commented Oct 13, 2018

vtkstef commented Nov 8, 2018

happynev commented Nov 13, 2018

MaksSieve commented Dec 3, 2018

sanjana-bhat commented Jan 15, 2019 • edited Loading

ersione commented Jan 22, 2019 • edited Loading

ghost commented Mar 30, 2020

codermrrob commented Jun 10, 2020 • edited Loading

andrew-property-xyz commented Jun 10, 2020 • edited Loading

serhatdirihan commented Nov 22, 2020

kean-logan commented Dec 9, 2020 • edited Loading

rasheedzrt commented Feb 25, 2021 • edited Loading

tolpp commented Jun 1, 2021

Better solution

raviraj-bhalerao commented Aug 7, 2023 • edited Loading

tolpp commented Aug 7, 2023

raviraj-bhalerao commented Aug 7, 2023

SpikeBlues commented Dec 5, 2016 •

edited

Loading

rafaelaazevedo commented Oct 10, 2018 •

edited

Loading

sanjana-bhat commented Jan 15, 2019 •

edited

Loading

ersione commented Jan 22, 2019 •

edited

Loading

codermrrob commented Jun 10, 2020 •

edited

Loading

andrew-property-xyz commented Jun 10, 2020 •

edited

Loading

kean-logan commented Dec 9, 2020 •

edited

Loading

rasheedzrt commented Feb 25, 2021 •

edited

Loading

raviraj-bhalerao commented Aug 7, 2023 •

edited

Loading