Six Stars

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

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.

 

 

2 REPLIES
Twelve Stars

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

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.

Rilhia Solutions
Six Stars

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

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