Six Stars

how to separate array elements of json block

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.

 

  • Big Data
7 REPLIES
Community Manager

Re: how to separate array elements of json block

 

Hi

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:

 

1.png2.png

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

 

----------------------------------------------------------
Talend | Data Agility for Modern Business
Six Stars

Re: how to separate array elements of json block

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?

Community Manager

Re: how to separate array elements of json block

Hi

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

----------------------------------------------------------
Talend | Data Agility for Modern Business
Four Stars

Re: how to separate array elements of json block

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
Six Stars

Re: how to separate array elements of json block

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?
Six Stars

Re: how to separate array elements of json block

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

Re: how to separate array elements of json block

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

1.png

----------------------------------------------------------
Talend | Data Agility for Modern Business