Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Join us in Toronto Sept 9th for Qlik's AI Reality Tour! Register Now
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

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.

 

Labels (3)
7 Replies
Anonymous
Not applicable
Author

 

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:

 

0683p000009Luin.png0683p000009LuZO.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

 

Anonymous
Not applicable
Author

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
Not applicable
Author

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
Not applicable
Author

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
Not applicable
Author

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
Not applicable
Author

I didn't understand. Could you please show any example?
Anonymous
Not applicable
Author

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

0683p000009LvKA.png