how to separate array elements of json block

[Anonymous]

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.

[Anonymous]

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:

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

[Anonymous]

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?

[Anonymous]

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

[Anonymous]

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

[Anonymous]

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?

[Anonymous]

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

[Anonymous]

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