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

[DaveTaylor]

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.

[Anonymous]

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.

[DaveTaylor]

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

[Gourav_King_of_DataLand]

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 "/>"."