jqjson-flattener

flattening json to csv with jqplay


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
            },
            ....

Solution

  • 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