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?

Answer 1

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