0

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
peak
  • 105,803
  • 17
  • 152
  • 177
Jacob Manu
  • 119
  • 10
  • Please follow the [mcve] guidelines, including a representative but short example rather than a link to another web site. – peak Nov 27 '19 at 03:52

1 Answers1

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"
peak
  • 105,803
  • 17
  • 152
  • 177
  • 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 – Jacob Manu Nov 27 '19 at 03:16
  • In the update to your Q, you call map(tostring) twice and use @csv twice as well! – peak Nov 27 '19 at 03:38