Converting a nested JSON into csv with jq

Question

I would like to convert the following JSON into a csv format using jq. I know there are tons of similar questions but I could not figure it out based on them.

{
    "one": {
        "firstName": "John",
        "lastName": "Smith",
        "gender": "man",
        "age": 32,
        "address": {
            "streetAddress": "21 2nd Street",
            "city": "New York",
            "state": "NY",
            "postalCode": "10021"
        }
    },
    "two": {
        "firstName": "Johnny",
        "lastName": "Smithy",
        "gender": "man",
        "age": 33,
        "address": {
            "streetAddress": "22 2nd Street",
            "city": "New York",
            "state": "NY",
            "postalCode": "10021"
        }
    }
}

The output should look like the following. I'm struggeling with the nested object as value of the address key.

number,firstName,lastName,gender,age,streetAddress,city,state,postalCode
one,John, Smith,man, 32, 21 2nd Street, New York, NY, 10021
two, Johnny, Smith,man, 33, 22 2nd Street, New York, NY, 10021

The Best I could do is the foillowing but it does not come close... Your help is much apreciated

jq --raw-output 'to_entries | map_values({ job: .key } + .value )'

Have you tried making an array out of scalars in each object and passing them to @csv filter? Like jq -r 'keys[] as $k | [ $k ] + [ .[$k] | getpath(paths(scalars)) ] | @csv' file — oguz ismail
– oguz ismail, Commented Jan 20, 2020 at 10:17
Thank you! that looks very promising. Do you know how I would also get the keys as a header row? like that: ` number,firstName,lastName,gender,age,streetAddress,city,state,postalCode ` — axel
– axel, Commented Jan 20, 2020 at 10:29
If it's constant, just pass it to the csv filter before everything else. Otherwise you can copy it from the first object — oguz ismail
– oguz ismail, Commented Jan 20, 2020 at 11:03
It will not be constant unfortunately, could you please show me how to do it in that case? — axel
– axel, Commented Jan 20, 2020 at 12:10

peak · Accepted Answer · 2020-01-21 09:08:28Z

2

For brevity, the following assumes that all the .address objects have the keys in the right order:

def headers: "number,firstName,lastName,gender,age,streetAddress,city,state,postalCode";

headers,
(to_entries[]
 | [.key,
    (.value 
     | (.firstName, .lastName, .gender, .age, .address[])) ]
 | join(",") )

Here, headershas been defined as a function so you can easily adapt it to yor needs, e.g. if the header strings should be based on the key names. In that case, though, the detsils will depend on what assumptions if any can be made about the uniformity of the objects.

edited Jan 21, 2020 at 9:08

answered Jan 21, 2020 at 7:34

peak

119k21 gold badges185 silver badges218 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Converting a nested JSON into csv with jq

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related