Convert json file with line break to CSV with jq command

※I turned Japanese articles written in Qiita into English.

Japanese:https://qiita.com/henatyokotraveler/items/297c7993d6b3e1fc2fb6

Situation

Convert the json file data to CSV.
An environment that takes it into elasticsearch via logstash (and an environment that displays it in Grafana).
* Should I use json as it is without CSV conversion, or use logstash? It hasn’t tried on the issue of his skill.
I get parce error in json, it is troublesome to adjust to the bulk format, and I am doing CSV input without thinking anything.

CSV conversion and importing it worked well,
There is data that includes line breaks, so I got stuck.

General CSV conversion with jq command

cat sample1.json | jq -r ‘. [] | [.key1, .key2, .key3] | @csv’

However, in this case, if there is data with line breaks in json,
Double quotes are removed by the -r option, and line breaks occur at the line feed code.
As a result, line breaks in CSV data were messed up, and elasticsearch data was strange or could not be imported.

Newline and -r (-raw-output) option

How can I convert a json that includes a line break to CSV without line breaks?
jq @csv, when googled, every site has a -r option.
However, if you add the -r option, the json double quotes will be removed at that point and line breaks will occur.

So I decided to convert to CSV without -r with sed command and convert to CSV with @csv.

Process sed command

Like this.
Actually, I made fine adjustments while comparing the data created in CSV with -r and without -r.
* So, I think that it is a measure specific to your environment, but as a methodology it is general purpose.

cat sample1.json | jq ‘. [] | [.key1, .key2, .key3] | @csv’ \
| sed ‘s / \\\\ / \\ / g’ \ #Because \ in each key value was \\, return to the original \
| sed ‘s / \\\ “/ \” / g’ \ #Return to “because each key value is enclosed in \” instead of “
| sed ‘s / ^ “// g’ \ #Delete double quotes at the beginning
| sed ‘s / “$ // g’ \ #Remove double quotes at the end
>> hoge.csv

For Copy and Paste(cleanup Disturbing comment)

cat sample1.json | jq ‘. [] | [.key1, .key2, .key3] | @csv’ \
| sed ‘s / \\\\ / \\ / g’ \
| sed ‘s / \\\ “/ \” / g’ \
| sed ‘s / ^ “// g’ \
| sed ‘s / “$ // g’ \
>> hoge.csv