2

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

3 Answers 3

2

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"
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.

1

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
  1. run it!
$  jq -rf x.jq  ex.json 
"name","id","tenant","vpc"
"NAME1","0b19d","184a5","0d11f"
"NAME2","0d34a","067eb","17944"
"NAME3","173e2","0ae21","071c4"

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.