tExtractJSONFields

[RVeitch_84]

I'm trying to use "tExtractJSONFields" and Read By Xpath to pasrse the following data and its trowing an error related to the "

Machine Name

"

Error on line 1 of document : Attribute name "Name" associated with an element type "Machine" must be followed by the ' = ' character.

[ERROR] 15:56:51 talendjobs.sqs_test_0_1.sqs_test- tExtractJSONFields_3 - Error on line 1 of document : Attribute name "Name" associated with an element type "Machine" must be followed by the ' = ' character.

Here is a snip of the Data that I'm trying to parse:

{

"TransactionId": 5031186299,

"RemoteStartTransactionId": null,

"PaymentMethodId": 1,

"SiteId": 4,

"MachineTime": "2023-02-20T08:32:56.953",

"Void": false,

"MachineId": 868467276,

"Data": {

"Machine Name": "81064",

"Operator Identifier": "K-07/81064"

}

}

I've tried this with no luck?

I think I have to some how add a space in the xpath query, but can't figure out the syntax ???

Any help would be appreciated.

[Fabio_Caimi]

Did you try "Machine Name" ?

[RVeitch_84]

Yes, thats what I started with, see screenshot.

same error?

[rhall1]

@Robert Veitch​ this answer gives you the solution. Look at how "Customer Service" is retrieved....

https://community.talend.com/s/feed/0D75b000007wSMJCA2

Note: JSON Path is used. Probably the better system to use here.

[RVeitch_84]

I started out with JSON Path which does work but it gives me sqaure brackets around every field ["81064"] and I have to insert all of this data into a DB so I wanted it without the brackets. 81064 and I have over 140 columns to parse.

I did find a workaround with Xpath, but would like to know if there is a work around for the [] issue in JSON Path?

["81061"] |["K-04\/81061"]  |["INTERAC"]

And I don't want to do something like this for 140 cloumns.

[rhall1]

I can only presume that your snippet above is part of an array and that maybe the loop path is not specified correctly when you are retrieving the values. Using just the snippet above and this JSON Path expression....

"Data.['Machine Name']"

....I get ....

81064

[RVeitch_84]

This is an example of what I was testing with:

 

That returned :

|5041105519  |null          |["81065"] |["K-08\/81065"]  |["INTERAC"]

[rhall1 · Accepted solution]

I *think* this is caused by your Json query for MachineName, OperatorIdentifier and Brand being slightly off. Take a look at the section you showed below....

{

"TransactionId": 5031186299,

"RemoteStartTransactionId": null,

"PaymentMethodId": 1,

"SiteId": 4,

"MachineTime": "2023-02-20T08:32:56.953",

"Void": false,

"MachineId": 868467276,

"Data": {

"Machine Name": "81064",

"Operator Identifier": "K-07/81064"

}

}

..."Machine Name" and "Operator Identifier" are sat within the "Data" object. You have left the "Data" object out of the path and just used ".". If you replace ".['Machine Name']" and ".['Operator Identifier']" with....

"Data.['Machine Name']"

"Data.['Operator Identifier']"

....that will fix those two.

I don't see "Brand" in the sample you have given, but I suspect this is due to something similar.

A good tool to get an idea for JSON Paths is this one.....

https://jsonpathfinder.com/

[RVeitch_84]

rhall

Thanks for the input, that helped!

|5041174753  |null          |81061   |K-04/81061    |VISA |

I will continue to test some more tomorrow.

[rhall1]

Glad to help. It can be a bit tricky to get your head around this stuff to start with, but once you've got a few things figured out, it makes much more sense moving forward 😉