topic Re: tExtractJsonFields - accessing a sibling field at the same level as the loop in Talend Studio

tExtractJsonFields - accessing a sibling field at the same level as the loop

DaveTaylor — Tue, 08 Aug 2017 00:29:15 GMT

sample json:

{
"response": {
"code": "200",
"description": "SUCCESS",
"type": "SUCCESS"
},
"agentPartyID": "XLB91",
"agencyId": "X0945",
"agencyName": "CRUMP LIFE INSURANCE",
"producerHierarchy": [{
   "primaryAgentPartyID": "XLB92"
   }, {
   "primaryAgentPartyID": "SM001"
   }, {
   "primaryAgentPartyID": "BBBBBBBBBB"
   }, {
   "primaryAgentPartyID": "X0559"
   }, {
   "primaryAgentPartyID": "X0945"
   }, {
   "primaryAgentPartyID": "FV062"
  }
]
}

I have other fields within the producerHierarchy loop besides the primaryAgentPartyID field; I just removed them for brevity.

Read By: JsonPath
Loop Jsonpath query: "$.producerHierarchy[*]"

Mapping:
Column Json query

primaryAgentPartyID "primaryAgentPartyID"

agentPartyID "../agentPartyID"

When I run the job; and the step executes; and I review the LogRow; it successfully finds all the primaryAgentPartyIDs; but the agentPartyID is blank.

Are you not allowed to recurse up the jsonpath chain above the loop field?

I've tried several jsonpath formats on the agentPartyID field; trying to get it included; but no luck. I would like my output to look like this:

|=-----------+--------------+-----
|primaryAgentPartyID|agentPartyID|
|=-----------+--------------+-----
|XLB92              |XLB91       |
|SM001              |XLB91       |
|BBBBBBBBBB         |XLB91       |
|X0559              |XLB91       |
|X0945              |XLB91       |
|FV062              |XLB91       |
'------------+--------------+-----

I feel like this should be simple; but I'm not seeing it.

Re: tExtractJsonFields - accessing a sibling field at the same level as the loop

Anonymous — Tue, 08 Aug 2017 01:27:28 GMT

Change your component to use XPath instead of JSONPath and use the following XPath config...

Loop : "/producerHierarchy"

Mapping:
Column XPath query

primaryAgentPartyID "./primaryAgentPartyID"

agentPartyID "../agentPartyID"

This will do exactly what you want.

Re: tExtractJsonFields - accessing a sibling field at the same level as the loop

DaveTaylor — Tue, 08 Aug 2017 03:55:33 GMT

Thanks
Didn't realize you could you xpath against a json input. Nice trick to remember.

Re: tExtractJsonFields - accessing a sibling field at the same level as the loop

Gourav_King_of_DataLand — Fri, 17 Aug 2018 11:04:27 GMT

Can this be done using Read by : JsonPath rather than Xpath? The issue that I am facing is in my output one of the element is named as "mime_type$string" this is causing the job to break and I get following error "Error on line 28 of document : Element type "mime_type" must be followed by either attribute specifications, ">" or "/>". Nested exception: Element type "mime_type" must be followed by either attribute specifications, ">" or "/>"."