Nested JSON to CSV using JQ

Question

I'm trying to convert this json https://pastebin.com/1ch1CzM4 to a csv so I can import it all into the db but I keep hitting errors. The closest I got was

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

However this didn't do the trick since it's nested. Would greatly appreciate any help.

I'm trying to get the parents as top row so "address, amenity_groups, check_in_time, check_out_time" and then if there's a json inside those it's just a flat json text in the csv.

If I try

jq -r '[[.address, .amenity_groups, .check_in_time, .check_out_time] | map(tostring) | @csv] | map(tostring) | @csv'

Then the output is

"""140 Ancien Chemin De Berre, Lambesc"",""[{""""amenities"""":[""""Garden"""",""""Terrace""""],""""group_name"""":""""General""""}]"",""16:00:00"",""10:00:00"""

vs the desired

address, amenity_groups, check_in_time, check_out_time
$json_of_address, $json_of_amenity_groups, 12, 12

Please follow the minimal reproducible example guidelines, including a representative but short example rather than a link to another web site. — peak
– peak, Commented Nov 27, 2019 at 3:52

peak · Accepted Answer · 2019-11-27 05:48:06Z

1

A solution that seems to meet the stated requirements would be:

[.address, .amenity_groups, .check_in_time, .check_out_time]
| map(tostring)
| @csv

You will probably want to avoid using tostring blindly, but at least it is generic.

Generic JSON-to-CSV conversion

A jq filter (json2csv) for converting an array of arbitrary JSON objects to CSV is available at:

jq: Object cannot be csv-formatted, only array

With your stream of JSON objects, you could (for example) use it like this with the -s command-line option:

map({address, amenity_groups, check_in_time, check_out_time})
| json2csv

Output using json2csv

"address","amenity_groups_0_amenities","amenity_groups_0_group_name","amenity_groups_1_amenities","amenity_groups_1_group_name","amenity_groups_2_amenities","amenity_groups_2_group_name","amenity_groups_3_amenities","amenity_groups_3_group_name","amenity_groups_4_amenities","amenity_groups_4_group_name","amenity_groups_5_amenities","amenity_groups_5_group_name","check_in_time","check_out_time"
"140 Ancien Chemin De Berre, Lambesc","Garden|Terrace","General","null","null","null","null","null","null","null","null","null","null","16:00:00","10:00:00"
"Tayside Farm 1077, East London","Garden|Smoking allowed in bedrooms|Patio","General","Fridge|Kitchen","Meals","Parking","Parking","Beach/pool towels","Pool and beach","TV|Shower","Rooms","Iron and board","Room Amenities","14:00:00","12:00:00"

edited Nov 27, 2019 at 5:48

answered Nov 27, 2019 at 2:54

peak

119k21 gold badges185 silver badges218 bronze badges

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

2 Comments

Jacob Manu Over a year ago

it's really close but it's giving me some weird outputs. if you have a sec would love for you to have a look. this has been driving me crazy

peak Over a year ago

In the update to your Q, you call map(tostring) twice and use @csv twice as well!

Collectives™ on Stack Overflow

Nested JSON to CSV using JQ

1 Answer 1

Generic JSON-to-CSV conversion

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Generic JSON-to-CSV conversion

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related