Parsing JSON with tExtractJSONFields

[Anonymous]

I have this JSON that I made up

{ "store": {
    "book": [ 
      { "category": "reference",
        "author": "Nigel Rees",
        "title": "Sayings of the Century",
        "price": 8.95
      },
      { "category": "fiction",
        "author": "Evelyn Waugh",
        "title": "Sword of Honour",
        "price": 12.99
      },
      { "category": "fiction",
        "author": "Herman Melville",
        "title": "Moby **bleep**",
        "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": [
			{ "EU":22.99},
			{ "US":72.3 },
			{ "BIH":5.80}
		]
      }
    ],
    "bicycle": {
      "color": "red",
      "price": 19.95
    }
  }
}

Is it possible that I can use tExtractJSONFields  to get every book in its own row ? How can I do that ?

My end goal the is to go through each of that row and get prices.

[Anonymous]

Yes, this is very possible.

Just set your "loop jsonPath query" to "$.store.book[*]" and then you can extract each of the entities within the book loop by simply placing the name exactly as it appears within quotes in the mapping section. For example....

"category"

"author"

"title"

"price"

[Anonymous]

I am facing similar issue. could you please suggest how to fetch data for color under Bicycle for every record using JSONpath. I am able to do it using XPath but facing issue fetching data from TRest --> TExtractJSONFeilds(using XPath)

So, I need a solution to fetch records from parent node. I wish to Loop the data under Book.

Please treat this as urgent !!!

[Anonymous]

I suspect that the issue here is that XPath allows you to traverse up and down the hierarchy (parent/child elements) and JsonPath does not allow you to traverse back to ancestors. A way of doing this with JsonPath is explained in this post....

https://community.talend.com/t5/Design-and-Development/downloading-JSON-in-structured-format/m-p/152...

[Anonymous]

Thank you so much for your response. I spent many hours struggling for this solution.
This is really helpful.

[Anonymous]

No problem at all 🙂

[Anonymous]

Hi @rhall 

I am facing one more issue with JSON parsing.

When I implement the logic to read data under Book, the records having empty array ( Book [] ) is getting filtered. 

The output I expect is, Parent record should be populated as it is having data for all columns and Null or [] for Book column.

PFA... output of tExtractJSON_10 should pass 55 or more records because of the loop. But all the records having "Book []" are getting rejected.

Please suggest to proceed. 

Record_Flow.PNG

[Anonymous]

Any suggestions on this would be very helpful... Awaiting for the response

[Anonymous]

I think this is because your initial "loop jsonPath query" is "$.store.book[*]". Since this returns nothing, there will be no data returned. You probably need a tExtractJSONFields component before one dealing with the book data and set it's "loop jsonPath query" to "$.store". You then need to retrieve the data you are missing AND the book array in different columns. Then in the following tExtractJSONFields component, extract your data from the book array column and simply pass the other data through with it. You can see an example of this here: https://community.talend.com/t5/Design-and-Development/JSON-Array-values-to-Rows-is-there-a-better-w...

[Anonymous]

Please refer the solution you provided for similar scenario which helped me till now 

https://community.talend.com/t5/Design-and-Development/downloading-JSON-in-structured-format/m-p/152...

Suppose, we have one more record with ActionID 410788 having array as NULL for Allocations[] , if I use the logic mentioned in this URL, its not loading any record for ActionID 410788.

But the o/p I expect is, a record into target with ActionID 410788 and AllocTargetID, Alloc_Fscl_yr as NULL

Expected o/p:
ActionID AllocTargetID Alloc_Fscl_yr
410788 NULL NULL
410766 82373 2018
410766 82378 2020

I/P:

Actions:[
{
"ActionID": 410788
"Allocations":[] 
}
{
"ActionID": 410766
"Allocations":[
{
"AllocTargetID": 82373,
"Fiscal_Year" : 2018
}
{
"AllocTargetID": 82378,
"Fiscal_Year" : 2020
}
] 
  }
]

My tExtractJSON_1 have -
Loop JSONPath query as "Actions[*]"
Column ActionID have JSON query as "$.ActionID"
Column Allocations have JSON query as "$.Allocations"

My tExtractJSON_2 have -
JSON Field as Actions
Loop JSONPath query as "$.[*]"
Column AllocTargetID have JSON query as "$.AllocTargetID"
Column Alloc_Fscl_yr have JSON query as "$.Fiscal_Year"

Your immediate suggestion would be very helpful