Issue with TextractJSONField - Read by Xpath

[Gourav_King_of_DataLand]

I have a requirement of pulling data from RestAPI and putting it into database. I created below mentioned job

tRestClient-->TextractJSONFields-->tLogRow-->tOracleOutput.

 Sample JSON

{
"chats":[
{

"id":"2001",
"comment": null,
"triggered_response": true,

"visitor":"Guest1",
"history": [
{
"name": "Guest1",
"msg_id": "1534494221340",
"msg": "Test message from Guest",
},
{
"name": "Support",
"msg_id": "1534494221340",
"msg": "Test message from support"
}
]
}
"count": 179049,
"next_url": "www.nexturl..."
}

The loop is on the history where I have to get all the chat messages for each visitor. For this I have used the

tExtractJSONField

Read By : Xpath 

Loop XPathQuery : "/chats/history[*]"

Mappin

Column                   xPathQuery

ChatID                   "../id"

Chat_Text              "msg"

Chat_Text_By       "name"

Everything works fine except sometimes the chat will have attachments and there are extra elements to 'history' and JSON looks like

{
"chats":[
{

"id":"2001"
"comment": null,
"triggered_response": true,
"history": [
{
"name": "Guest1",
"msg_id": "1534494221340",
"msg": "Test message from Guest",
},
{
"name": "Support",
"msg_id": "1534494221340",
"msg": "Test message from support"
}

"attachment": {
"mime_type$string": "image/jpeg",
"name$string": "image.jpg",
"url$string": "image_url"
}

]
}
"count": 179049,
"next_url": "www.nexturl..."
}

whenever this appears in output the job gives 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 "/>".

When I use Read By : JsonPath and supply JSON query there is no error but the parent elements to looping elements do not appear in result

tExtractJSONField Properties

Read By : JsonPath

Loop XPathQuery : "$.chats[*].history[*]"

Mappin

Column                   Json Query

ChatID                  "$.chats[*].id"

Chat_Text              "msg"

Chat_Text_By       "name"

So there are two issues I am looking for help

1) tExtractJSONFields with XPath option gives error when JASON output contains "$" in element name

2) tExtractJSONFields with JsonPath option does not give parent element data when loop jasonpath query is used

[Anonymous]

Hello,

Have you tried to use tfileinputjson component with XPath option and JsonPath to read your sample json to see if this two issues repro?

Best regards

Sabrina