flattening json to csv with jqplay

Question

I'm trying to flatten a json file to .csv. I'd like to use jqplay for this in stead of programming it in python for example. The example below is een array that als contains arrays. My desired output is one line entry on the 2nd array: so

OPEN, NR1, ....

CLOSED, NR2, ...

....

Can anyone help me with a good jq command for this?

[
    {
        "description": "Berendrechtsluis",
        "lock_id": "BES",
        "longitude_wgs84": 4.28561,
        "latitude_wgs84": 51.34414,
        "lock_doors": [
            {
                "state": "OPEN",
                "lock_door_id": "NR1",
                "operational_state": "NO_DATA",
                "state_since_in_utc": "2021-12-29T16:32:23Z",
                "longitude_wgs84": 4.28214,
                "latitude_wgs84": 51.34426
            },
            {
                "state": "CLOSED",
                "lock_door_id": "NR2",
                "operational_state": "WORKING",
                "state_since_in_utc": "2022-01-12T12:32:52Z",
                "operational_state_since_in_utc": "2021-12-22T13:13:57Z",
                "longitude_wgs84": 4.28247,
                "latitude_wgs84": 51.34424
            },
            ....

[jq](https://stedolan.github.io/jq/) "is a lightweight and flexible command-line JSON processor", [jqplay](https://jqplay.org/) is a "playground for jq". — pmf, Jan 13 '22 at 10:28

pmf · Accepted Answer · 2022-01-13T15:36:22.417

0

Are you looking for something like this?

jq -r '.[].lock_doors[] | [.[]] | @csv'

"OPEN","NR1","NO_DATA","2021-12-29T16:32:23Z",4.28214,51.34426
"CLOSED","NR2","WORKING","2022-01-12T12:32:52Z","2021-12-22T13:13:57Z",4.28247,51.34424

Demo

To add column headers, simply prepend them in an array:

jq -r '["a","b","c"], .[].lock_doors[] | [.[]] | @csv'

"a","b","c"
"OPEN","NR1","NO_DATA","2021-12-29T16:32:23Z",4.28214,51.34426
"CLOSED","NR2","WORKING","2022-01-12T12:32:52Z","2021-12-22T13:13:57Z",4.28247,51.34424

Demo

edited Jan 13 '22 at 15:36

answered Jan 13 '22 at 10:22

pmf

24,478
2
22
31

Thanks pmf, that answers my question exactly. – GertVdW Jan 13 '22 at 14:24
Is there any easy fix to include description on each line? (I know, this goes beyond my initial question). – GertVdW Jan 13 '22 at 14:25
@GertVdW Do you mean column headers? Sure, prepend them in an array, see my update. – pmf Jan 13 '22 at 15:37
I actually mean an extra collum. So: "Berendrechtsluis", "OPEN","NR1","NO_DATA","2021-12-29T16:32:23Z",4.28214,51.34426 "Berendrechtsluis", "CLOSED","NR2","WORKING","2022-01-12T12:32:52Z","2021-12-22T13:13:57Z",4.28247,51.34424, – GertVdW Jan 14 '22 at 10:20
@GertVdW Then inject it where the columns are generated: `.[].lock_doors[] | ["Berendrechtsluis", .[]] | @csv`. [Demo](https://jqplay.org/s/d9zxEhsNxa) – pmf Jan 14 '22 at 11:54

flattening json to csv with jqplay

1 Answers1