Wednesday, 28 August 2013

JSONPath query returns same-named keys from nested objects when I only want the parent object

JSONPath query returns same-named keys from nested objects when I only
want the parent object

I'm currently working with Pentaho Kettle for some ETL jobs, and I need to
integrate a JSON feed, which means I need to use JSONPath to grab data.
For the most part, it's working well, except some of the JSON data is
nested objects with the same field name in both parent and child.
Example JSON:
[
{
"Key": "5e59d536-2e3c-487c-bff1-efd0a706532f",
"Product": {
"Name": "Some Product",
"LastUpdated": "2013-08-23T12:10:25.454",
},
"Reviewer": {
"Email": "blah@foo.com",
"LastUpdated": "2013-08-23T12:10:25.454",
},
"LastUpdated": "2013-08-23T12:10:25.407",
},
{
"Key": "f3ae6a4b-1a20-4a9a-9a8e-2de5949c4493",
"Product": {
"Name": "Some Product",
"LastUpdated": "2013-08-23T12:10:51.896",
},
"Reviewer": {
"Email": "blah@foo.com",
"LastUpdated": "2013-08-23T12:10:51.896",
},
"LastUpdated": "2013-08-23T12:10:51.896",
},
{
"Key": "de01c358-6c74-473c-8cd4-a44cf50132df",
"Product": {
"Name": "Some Product",
"LastUpdated": "2013-08-26T10:30:13.617",
},
"Reviewer": {
"Email": "blah@foo.com",
"LastUpdated": "2013-08-26T10:30:13.617",
},
"LastUpdated": "2013-08-26T10:30:13.601",
},
},
{
"Key": "af04e48a-3ce8-4227-a00a-14483ca75058",
"Product": {
"Name": "Some Product",
"LastUpdated": "2013-08-26T10:31:20.573",
},
"Reviewer": {
"Email": "blah@foo.com",
"LastUpdated": "2013-08-26T10:31:20.573",
},
"LastUpdated": "2013-08-26T10:31:20.573",
},
{
"Key": "d1a787bb-37d2-4ea9-84fd-5a3d454b9127",
"Product": {
"Name": "Some Product",
"LastUpdated": "2013-08-27T11:59:56.777",
},
"Reviewer": {
"Email": "blah@foo.com",
"LastUpdated": "2013-08-27T11:59:56.777",
},
"LastUpdated": "2013-08-27T11:59:56.73",
},
{
"Key": "d8646319-af27-464f-bd50-d61e035800c6",
"Product": {
"Name": "Some Product",
"LastUpdated": "2013-08-27T19:43:06.928",
},
"Reviewer": {
"Email": "blah@foo.com",
"LastUpdated": "2013-08-27T19:43:06.928",
},
"LastUpdated": "2013-08-27T19:43:06.866",
},
]
As you can see, the parent object, and its child objects "Product" and
"Reviewer" all have "LastUpdated" fields. I'm trying to get the parent
object's "LastUpdated" only, but using:
$..LastUpdated
returns, in order, the parent LastUpdated, Product LastUpdated, then
Reviewer LastUpdated.
RESULTS:
[
"2013-08-23T12:10:25.407",
"2013-08-23T12:10:25.454",
"2013-08-23T12:10:25.454",
"2013-08-23T12:10:51.896",
"2013-08-23T12:10:51.896",
"2013-08-23T12:10:51.896",
"2013-08-26T10:30:13.601",
"2013-08-26T10:30:13.617",
"2013-08-26T10:30:13.617",
"2013-08-26T10:31:20.573",
"2013-08-26T10:31:20.573",
"2013-08-26T10:31:20.573",
"2013-08-27T11:59:56.73",
"2013-08-27T11:59:56.777",
"2013-08-27T11:59:56.777",
"2013-08-27T19:43:06.866",
"2013-08-27T19:43:06.928",
"2013-08-27T19:43:06.928"
]
EXPECTED RESULTS:
[
"2013-08-23T12:10:25.407",
"2013-08-23T12:10:51.896",
"2013-08-26T10:30:13.601",
"2013-08-26T10:31:20.573",
"2013-08-27T11:59:56.73",
"2013-08-27T19:43:06.866",
]
Is there a query I can use to only get the parent objects' LastUpdated
fields?

No comments:

Post a Comment