Convert complex JSON (with arrays and different data types) to CSV using JQ?

Question

I have the following JSON data:

   {
        "status": "ok",
        "ok": true,
        "data": "MFR-L",
        "stores": [{
            "name": "KOLL",
            "lat": 52.93128,
            "lng": 6.962956,
            "dist": 1,
            "x10": 1.129,
            "isOpen": true
        },
        {
            "name": "Takst",
            "lat": 52.9523773,
            "lng": 6.981644,
            "dist": 1.3,
            "x10": 1.809,
            "isOpen": false
        }]
    }

I'm trying to convert it to a flat file using JQ, but I keep running into all sorts of problems, especially because of the file types ("cannot index boolean with string", etc).

This post has helped me flatten the contents of the array so far, like this:

jq -r -s 'map(.stores | map({nm: .name, lt: .lat} | [.nm, .lt])) | add [] | @csv

How can I get the contents higher up in the hierarchy to map to the array contents?

Jeff Mercado · Accepted Answer · 2016-09-05 17:00:36Z

5

You could always collect the values you want from the parent objects separately from the child objects and combine them later.

e.g.,

$ jq -r '[.data] + (.stores[] | [.name, .lat, .lng, .dist]) | @csv' input.json

yields

"MFR-L","KOLL",52.93128,6.962956,1
"MFR-L","Takst",52.9523773,6.981644,1.3

answered Sep 5, 2016 at 17:00

Jeff Mercado

136k34 gold badges268 silver badges282 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Cos Over a year ago

Hi Jeff. This throws "Cannot iterate over null (null)" . I have to point out that I am using jq 1.4 .

Cos Over a year ago

Hello. Nevermind, you are absolutely right, I had a small mistake. This works, thanks!

peak · Accepted Answer · 2016-09-07 17:57:35Z

1

There are several ways in which the illustrative JSON might be "flattened" (e.g. to CSV), but the following two approaches may be of interest. (I've omitted the invocation of @csv for ease-of-reading.) $ jq '[.data, .stores[][]]' in.json [ "MFR-L", "KOLL", 52.93128, 6.962956, 1, 1.129, true, "Takst", 52.9523773, 6.981644, 1.3, 1.809, false ]

$ jq '.data as $data | .stores[] | [$data, .[]]' in.json [ "MFR-L", "KOLL", 52.93128, 6.962956, 1, 1.129, true ] [ "MFR-L", "Takst", 52.9523773, 6.981644, 1.3, 1.809, false ]

answered Sep 7, 2016 at 17:57

peak

119k21 gold badges185 silver badges218 bronze badges

Comments

jq170727 · Accepted Answer · 2017-08-28 05:04:27Z

0

Here is another approach which uses jq variables and string interpolation:

   .data as $data
 | .stores[]
 | "\($data),\(.name),\(.lat),\(.lng),\(.dist),\(.x10),\(.isOpen)"

output with sample data:

"MFR-L,KOLL,52.93128,6.962956,1,1.129,true"
"MFR-L,Takst,52.9523773,6.981644,1.3,1.809,false"

answered Aug 28, 2017 at 5:04

jq170727

15.1k6 gold badges52 silver badges61 bronze badges

Collectives™ on Stack Overflow

Convert complex JSON (with arrays and different data types) to CSV using JQ?

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related