JSONPath syntax for tFileInputJSON

Anonymous — Sat, 16 Nov 2024 13:27:21 GMT

I am having a problem getting my JSONPAth query correct to extract complete records using tFileInputJSON
This configuration seems to work:
Mapping
Column JSONPath Query
id $..id
name $..name
....
film_title $..customFields
HOWEVER, not every item has all customFields.
So the output record contains the next available film_title, not the film_title for that item.
I know I need to somehow extract the fields for a particular item, rather than retrieve all elements of each type as the $.. does, but unlike the XML components there seems no way of selecting a loop element. The documentation at http://goessner.net/articles/JsonPath/ has not helped either.
Can anyone point me in the right direction?
Thanks
JSON Sample
{
"items": [
{
"id": 73654804001,
"name": "Ninja Assassin - TV trailer",
"shortDescription": "From the people who brought you The Matrix ",
"longDescription": "An ultra-violent ninja odyssey about an ancient art of assassins.",
"videoStillURL": "http:\/\/media\/TV-trailer.jpg?pubId=64298585001",
"thumbnailURL": "http:\/\/media\/image.jpg?pubId=64298585001",
"referenceId": "Movies_12518899",
"customFields": {
"sub_channel": "Trailer",
"film_title": "Ninja Assassin",
"movie_genre": "Action \/ Adventure",
"release_date": "22\/01\/2010",
"actors": "Rain, Naomie Harris, Ben Miles, Sho Kosugi, Rick Yune",
"director": "James McTeigue",
"channel": "Movies"
}
},
..................

Re: JSONPath syntax for tFileInputJSON

Anonymous — Fri, 23 Apr 2010 18:27:42 GMT

Both
$..customFields
and
$.items.
work to select the JSON array of custom fields:
{"sub_channel":"Movie shows","movie_genre":"Top 10s","show_series":"Top 10s","channel":"Movies"}
But I can't suss the syntax to extract movie_genre of "Top 10s" or film_title of ""
I haven't got the @ syntax to work for me.
The sum total of JSONPath examples on the Web is the same bookstore example. Perhaps if I knew Xpath better I would understand.
Help please - I'll keep experimenting

topic JSONPath syntax for tFileInputJSON in Talend Studio

JSONPath syntax for tFileInputJSON

Re: JSONPath syntax for tFileInputJSON