topic Re: tExtractJSONFields in Talend Studio

tExtractJSONFields

RVeitch_84 — Fri, 15 Nov 2024 22:04:56 GMT

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.

Re: tExtractJSONFields

Fabio_Caimi — Tue, 21 Feb 2023 12:33:39 GMT

Did you try "Machine Name" ?

Re: tExtractJSONFields

RVeitch_84 — Tue, 21 Feb 2023 13:30:38 GMT

Yes, thats what I started with, see screenshot.

same error?

Re: tExtractJSONFields

rhall1 — Wed, 22 Feb 2023 01:01:27 GMT

@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.

Re: tExtractJSONFields

RVeitch_84 — Wed, 22 Feb 2023 21:09:56 GMT

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.

Re: tExtractJSONFields

rhall1 — Wed, 22 Feb 2023 21:58:55 GMT

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

Re: tExtractJSONFields

RVeitch_84 — Wed, 22 Feb 2023 22:26:25 GMT

This is an example of what I was testing with:

That returned :

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

Re: tExtractJSONFields

rhall1 — Wed, 22 Feb 2023 22:39:50 GMT

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/

Re: tExtractJSONFields

RVeitch_84 — Wed, 22 Feb 2023 22:53:59 GMT

rhall

Thanks for the input, that helped!

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

I will continue to test some more tomorrow.

Re: tExtractJSONFields

rhall1 — Wed, 22 Feb 2023 22:59:24 GMT

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 😉