How to create CSV with headers from JSON using jq

Question

I am trying to create a csv file with headers from a json using jq.

I have the following snippet https://jqplay.org/s/H_U5mxbTFW

Question: How can I print the values of peerings > accept_vpc_info > tenant_id and peerings > accept_vpc_info > vpc_id

What i tried:

The JSON

{
    "peerings": [
        {
            "accept_vpc_info": {
                "tenant_id": "184a5",
                "vpc_id": "0d11f"
            },
            "created_at": "2018-11-07T08:53:43",
            "description": null,
            "id": "0b19d",
            "name": "NAME1",
            "request_vpc_info": {
                "tenant_id": "08a7b",
                "vpc_id": "1645f"
            },
            "status": "ACTIVE",
            "updated_at": "2018-11-07T09:47:44"
        },
        {
            "accept_vpc_info": {
                "tenant_id": "067eb",
                "vpc_id": "17944"
            },
            "created_at": "2019-06-12T08:29:08",
            "description": null,
            "id": "0d34a",
            "name": "NAME2",
            "request_vpc_info": {
                "tenant_id": "0fd7b",
                "vpc_id": "102c9"
            },
            "status": "ACTIVE",
            "updated_at": "2019-06-12T12:04:56"
        },
        {
            "accept_vpc_info": {
                "tenant_id": "0ae21",
                "vpc_id": "071c4"
            },
            "created_at": "2019-02-21T09:07:35",
            "description": null,
            "id": "173e2",
            "name": "NAME3",
            "request_vpc_info": {
                "tenant_id": "05a21",
                "vpc_id": "0586e"
            },
            "status": "ACTIVE",
            "updated_at": "2019-02-21T09:10:41"
        }
            ]
    }

JQ:

.[] | flatten | map({created_at,description,id,name,status,updated_at}) | (first | keys_unsorted) as $keys | map([to_entries[] | .value]) as $rows | $keys,$rows[] | join (", ")

It is working without adding accept_vpc_info and request_vpc_info.

I try to add these as header: accept_vpc_info, accept_vpc_info_tenant_id, accept_vpc_info_vpc_id, created_at, description, id, name,request_vpc_info, request_vpc_info_tenant_id, request_vpc_info_vpc_id, status, updated_at

Something like this:

"accept_vpc_info__tenant_id","accept_vpc_info__vpc_id","created_at","description","id","name","request_vpc_info__tenant_id","request_vpc_info__vpc_id","status","updated_at"
"184a5","0d11f","2018-11-07T08:53:43","null","0b19d","NAME1","08a7b","1645f","ACTIVE","2018-11-07T09:47:44"
"067eb","17944","2019-06-12T08:29:08","null","0d34a","NAME2","0fd7b","102c9","ACTIVE","2019-06-12T12:04:56"
"0ae21","071c4","2019-02-21T09:07:35","null","173e2","NAME3","05a21","0586e","ACTIVE","2019-02-21T09:10:41"

How can I print also the values of peerings > accept_vpc_info > tenant_id and peerings > accept_vpc_info > vpc_id ?

Thank you

Answer 1

I generally dislike using two tools when one should do the job, but you might consider using Miller's (limited) JSON to CSV conversion capabilities - this seems to be one case where the limitations actually help (since it flattens everything by default, and constructs the flattened field names for you):

$ mlr --prepipe 'jq ".[]"' --ijson --jflatsep __  --ocsv --quote-all cat file.json
"accept_vpc_info__tenant_id","accept_vpc_info__vpc_id","created_at","description","id","name","request_vpc_info__tenant_id","request_vpc_info__vpc_id","status","updated_at"
"184a5","0d11f","2018-11-07T08:53:43","","0b19d","NAME1","08a7b","1645f","ACTIVE","2018-11-07T09:47:44"
"067eb","17944","2019-06-12T08:29:08","","0d34a","NAME2","0fd7b","102c9","ACTIVE","2019-06-12T12:04:56"
"0ae21","071c4","2019-02-21T09:07:35","","173e2","NAME3","05a21","0586e","ACTIVE","2019-02-21T09:10:41"

Answer 2

Using csvkit, you can translate directly Json to CVS

$ in2csv -k peerings ex.json
accept_vpc_info/tenant_id,accept_vpc_info/vpc_id,created_at,description,id,name,request_vpc_info/tenant_id,request_vpc_info/vpc_id,status,updated_at
184a5,0d11f,2018-11-07T08:53:43,,0b19d,NAME1,08a7b,1645f,ACTIVE,2018-11-07T09:47:44
067eb,17944,2019-06-12T08:29:08,,0d34a,NAME2,0fd7b,102c9,ACTIVE,2019-06-12T12:04:56
0ae21,071c4,2019-02-21T09:07:35,,173e2,NAME3,05a21,0586e,ACTIVE,2019-02-21T09:10:41

See in2csv manual for more options and details.

Answer 3

With jq you could run something along the lines of:

1. (Assuming you want to select just some fields) create a jq filter "x.jq"

["name","id","tenant","vpc"] ,      ### the csv header
(.peerings[]                        ### for all in peerings list
  | [ .name,
      .id,
      .accept_vpc_info.tenant_id,
      .accept_vpc_info.vpc_id 
    ]
) | @csv

2. run it!

$  jq -rf x.jq  ex.json 
"name","id","tenant","vpc"
"NAME1","0b19d","184a5","0d11f"
"NAME2","0d34a","067eb","17944"
"NAME3","173e2","0ae21","071c4"