topic Parsing JSON with tExtractJSONFields in Talend Studio

Parsing JSON with tExtractJSONFields

Anonymous — Sat, 16 Nov 2024 09:24:22 GMT

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.

Re: Parsing JSON with tExtractJSONFields

Anonymous — Wed, 16 Aug 2017 20:41:18 GMT

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"

Re: Parsing JSON with tExtractJSONFields

Anonymous — Wed, 06 Mar 2019 16:42:13 GMT

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 !!!

Re: Parsing JSON with tExtractJSONFields

Anonymous — Wed, 06 Mar 2019 17:47:53 GMT

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/152397/highlight/true#M93137

Re: Parsing JSON with tExtractJSONFields

Anonymous — Thu, 07 Mar 2019 15:24:51 GMT

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

Re: Parsing JSON with tExtractJSONFields

Anonymous — Thu, 07 Mar 2019 15:39:50 GMT

No problem at all 🙂

Re: Parsing JSON with tExtractJSONFields

Anonymous — Fri, 26 Apr 2019 14:58:35 GMT

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

Re: Parsing JSON with tExtractJSONFields

Anonymous — Mon, 29 Apr 2019 10:01:48 GMT

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

Re: Parsing JSON with tExtractJSONFields

Anonymous — Mon, 29 Apr 2019 10:22:20 GMT

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-way/m-p/52347/highlight/true#M17361

Re: Parsing JSON with tExtractJSONFields

Anonymous — Mon, 29 Apr 2019 12:34:29 GMT

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/152397/highlight/true#M93137

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

Re: Parsing JSON with tExtractJSONFields

Anonymous — Mon, 29 Apr 2019 17:54:06 GMT

I see your problem. First of all, you have a problem with your JSON. I think this is what your JSON should look like.....

{  
   "Actions":[  
      {  
         "ActionID":410788,
         "Allocations":[  

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

The JSON you posted is not valid JSON.

Apart from that issue (I assume caused by copying and pasting), you do have a point in that it is difficult to push rows through a tExtractJSONField component where the "Look JSONPath Query" returns nothing. This is the case for your empty array. But there is a workaround for this. I built a job as shown below...

The tJavaFlex is just where I added your JSON. I won't show that. The example I used is above.

The first tExtractJSONFields component looks like below.....

This should be pretty easy to understand.

The tJavaRow is where the workaround takes place. The code looks like this.....

Essentially, what I am doing is passing through the ActionID as normal, but adding ....

{"Allocations":

....and ....

...around the array "[]" passed on. If the array looks like this "[]" I am substituting it with this....

[empty]

What this does is cause the tExtractJSONField component to try to process the array. It won't find anything for "empty", but it means that a row is passed through anyway. This row carries your ActionID.

The tExtractJSONField component after the tJavaRow looks like this...

Pay attention to the JSONPaths.

When it runs, the following is output....

Re: Parsing JSON with tExtractJSONFields

Anonymous — Mon, 29 Apr 2019 20:33:13 GMT

Thank you for the details. Will give a try and let you know.

My json have private information, so just modified your existing json and
pasted here.

Re: Parsing JSON with tExtractJSONFields

Anonymous — Mon, 29 Apr 2019 22:34:45 GMT

I thought there would be a reason for the JSON not being quite correct. Let me know how you get on 🙂

Re: Parsing JSON with tExtractJSONFields

Anonymous — Thu, 30 May 2019 14:56:14 GMT

@rhall Your suggestion to use tJavaRow for Null check worked like a charm. Thank you so much.

Note: I do not find any difference in the functionality with the value used for 'Loop JSON Path query' as "$.Allocations[*]" or "$.[*]" having JSON Field as Allocations

Thanks a lot again

Re: Parsing JSON with tExtractJSONFields

PITadmin — Tue, 17 Mar 2020 14:31:24 GMT

Hi,

i have a similar problem with extracting JSON.

I get a REST API response that looks like this but my tExtractJSONFields delivers 0 rows.

When i try $.[*].basicData.firstName @http://www.jsonquerytool.com/ i get the correct data.

thx in advance for your ideas

Stefan

[
  {
    "updateFields": null,
    "id": 2,
    "externalIdentifier": null,
    "externalVersion": null,
    "externalSource": null,
    "entityVersion": 35,
    "basicData": {
      "updateFields": null,
      "firstName": "Renate",
      "firstName2": null,
      "lastName": "Test",
      "personTypes": [
        "Employee"
      ],
      "companyMembershipIds": [],
      "profilePictureUuid": "f4377dbb-3ab0-4565-a1b3-856d22c90a6d",
      "deleted": false
    },
    "personalData": null,
    "privateData": null,
    "privacySettings": null,
    "assignmentsOverview": null,
    "user": {
      "updateFields": null,
      "id": 2,
      "enabled": true,
      "username": "rtest",
    },
    "currentCareers": null,
    "pastCareers": null,
    "futureCareers": null
  },
  {
    "updateFields": null,
    "id": 3,
    "externalIdentifier": null,
    "externalVersion": null,
    "externalSource": null,
    "entityVersion": 18,
    "basicData": {
      "updateFields": null,
      "firstName": "Stefan",
      "firstName2": null,
      "lastName": "Muster",
      "personTypes": [
        "Employee"
      ],
      "companyMembershipIds": [],
      "profilePictureUuid": "68b76185-0d1e-4301-b1e7-cd8db30496c6",
      "deleted": false
    },
    "personalData": null,
    "privateData": null,
    "privacySettings": null,
    "assignmentsOverview": null,
    "user": {
      "updateFields": null,
      "id": 5,
      "enabled": true,
      "username": "smuster",
    },
    "currentCareers": null,
    "pastCareers": null,
    "futureCareers": null
  }

Re: Parsing JSON with tExtractJSONFields

PITadmin — Wed, 18 Mar 2020 09:52:29 GMT

Just found out that my tRestClient does not deliver JSON but XML!?

Why?

Re: Parsing JSON with tExtractJSONFields

Anonymous — Wed, 18 Mar 2020 10:45:46 GMT

What is the configuration of your tRestClient? In the "Advanced Settings" do you have "Convert Response To DOM Document" ticked?

Re: Parsing JSON with tExtractJSONFields

PITadmin — Wed, 18 Mar 2020 13:31:49 GMT

Yes that helped - now it ist responding in JSON format

Thx a lot

Stefan

Re: Parsing JSON with tExtractJSONFields

swetha_molaka1 — Thu, 15 Jun 2023 17:37:36 GMT

Hello All,

I want to implement if then else condition in the schema mapping .( Eg: if id=245 then firstname else null)

I tried using it fieldValues[?(@.id==2645)].value to fetch value fieldValues if id==2645.

The result is coming up as []. I am expecting some value but getting empty array.

Sample json:

{

"type": customobject,

"id":1234,

"fieldValues": [

{

"type":FieldValue

"id":2654

"value": carl

{

"type":FieldValue

"id":2655

"value": Maxx

{

"type":FieldValue

"id":2656

"value": software Engineer

{},

"iSMapped":yes

"uniqcode":"CONT-23333"

}

Please help me its urgent.

Re: Parsing JSON with tExtractJSONFields

Jennifer_siders — Fri, 24 Nov 2023 10:54:25 GMT

Thanks! it helped