topic how to separate array elements of json block in Talend Studio

how to separate array elements of json block

Anonymous — Sat, 16 Nov 2024 09:38:02 GMT

Hi,

I have a sample json block as below --

{
"firstName": "John",
"lastName" : "doe",
"age"      : 26,
"address" : {
    "streetAddress": "naist street",
    "city"         : "Nara",
    "postalCode"   : "630-0192"
},
"phoneNumbers": [
    {
      "type" : "iPhone",
      "number": "0123-4567-8888"
    },
    {
      "type" : "home",
      "number": "0123-4567-8910"
    }
]
}

And my job as follows --

tFileInputJson-->tExtractJsonFields-->tMap-->tWriteJsonField-->tFileOutputDelimited

To extract "type" and "number" elements at tExtractJsonFields component, I am using Json query respectively as---

"$.phoneNumbers[*].type"

"$.phoneNumbers[*].number"

After my job finishes I need the output just like the input block. But instead I get as below --

{
"firstName": "John",
"lastName" : "doe",
"age"      : 26,
"address" : {
    "streetAddress": "naist street",
    "city"         : "Nara",
    "postalCode"   : "630-0192"
},
"phoneNumbers":
    {
      "type" : [ "iPhone","home"],
      "number":[ "0123-4567-8888","0123-4567-8910"]
    }}

What do I do to achieve separate type/number values in the output? What changes do I make changes and in which components?

Please reply. Thanks in advance.

Re: how to separate array elements of json block

Anonymous — Wed, 14 Jun 2017 08:53:53 GMT

If it is a json file, you can use tFileInputJson (without tExtractJsonField) to extract data from the file.On tWriteJsonField, you need to set phoneNumber as loop element and set group by columns, see:

This is the result on the console:

Starting job forum27908 at 15:49 14/06/2017.

[statistics] connecting to socket on port 3524
[statistics] connected
{"firstName":"John","lastName":"doe","age":"26","address":{"streetAddress":"naist street","city":"Nara","postalCode":"630-0192","phoneNumbers":[{"type":"iPhone","number":"31-10-9268"},{"type":"home","number":"31-10-9290"}]}}
[statistics] disconnected
Job forum27908 ended at 15:49 14/06/2017. [exit code=0]

Regards

Shong

Re: how to separate array elements of json block

Anonymous — Wed, 14 Jun 2017 10:06:15 GMT

I did exactly what you've said. I had put phoneNumbers as loop element and also had set group by columns (type & number). I still didn't get the required output. And I have to use tExtractJsonFields component because the input component may change in my job, also I need to perform some transformations on the columns using tMap. What do I do?

Re: how to separate array elements of json block

Anonymous — Wed, 14 Jun 2017 10:32:05 GMT

Which version of studio are you using? Can you export your job and upload it? It will be helpful for me to check what you are doing wrong in your job.

Regards

Shong

Re: how to separate array elements of json block

Anonymous — Wed, 14 Jun 2017 21:16:36 GMT

Try to map your json to tXMlMap and create 2 attribute to phoneNumber i.e. Type and number and loop the phoneNumber column but do not group Type and number

Re: how to separate array elements of json block

Anonymous — Fri, 16 Jun 2017 13:28:49 GMT

I am afraid I cannot upload the job. I have tried various examples but I am getting the similar output i.e. for instance, {"City" : ["Fallon"]} instead of [{City : "Fallon"}]
Could you please tell me if there is an array element like in this example, what xpath query/json query to be written in tExtractJsonFields in order to retrieve the required output?

Re: how to separate array elements of json block

Anonymous — Fri, 16 Jun 2017 13:29:27 GMT

I didn't understand. Could you please show any example?

Re: how to separate array elements of json block

Anonymous — Tue, 20 Jun 2017 08:20:52 GMT

Hello
In this case, the phoneNumbers is an array element. You need to set Loop XPATH query to "/phoneNumbers", see