2

I have the following json file which I would like to convert to csv:

{
  "id": 1,
  "date": "2014-05-05T19:07:48.577"
}
{
  "id": 2,
  "date": null
}

Converting it to csv with the following jq produces:

$ jq -sr '(map(keys) | add | unique) as $cols | map(. as $row | $cols | map($row[.])) as $rows | $cols, $rows[] | @csv' < test.json
"date","id"
"2014-05-05T19:07:48.577",1
,2

Unfortunately, for the line with "id" equal to "2", the date column was not set to "null" - instead, it was empty. This in turn makes MySQL error on import if it's a datetime column (it expects a literal "null" if we don't have a date, and errors on "").

How can I make jq print the literal "null", and not ""?

ikegami
  • 367,544
  • 15
  • 269
  • 518
Tomasz
  • 179
  • 12
  • I've edited in the `-s` option so that the question, example input and output are all consistent. If that wasn't the intent, feel free to re-edit. – Weeble Jun 20 '21 at 21:06

3 Answers3

3

I'd go with:

(map(keys_unsorted) | add | unique) as $cols
| $cols,
  (.[] | [.[$cols[]]] | map(. // "null") )
| @csv

First, using keys_unsorted avoids useless sorting.

Second, [.[$cols[]]] is an important, recurrent and idiomatic pattern, used to ensure an array is constructed in the correct order without resorting to the reduce sledge-hammer.

Third, although map(. // "null") seems to be appropriate here, it should be noted that this expression will also replace false with "null", so, it would not be appropriate in general. Instead, to preserve false, one could write map(if . == null then "null" else . end).

Fourth, it should be noted that using map(. // "null") as above will also mask missing values of any of the keys, so if one wants some other behavior (e.g., raising an error if id is missing), then an alternative approach would be warranted.

The above assumes the stream of JSON objects shown in the question is "slurped", e.g. using jq's -s command-line option.

peak
  • 105,803
  • 17
  • 152
  • 177
  • That idiom is one I had not encountered yet - nice! @Tomasz - if you want the value `false` to convert to "null", use `// "null"` as above; if you want `false` to convert to "false", then replace `// "null"` with `| tostring` (though that would also stringify numbers). – Joe Casadonte Jun 19 '21 at 13:09
1

Use // as alternative operator for your cell value:

jq -r '(map(keys) | add | unique) as $cols | map(. as $row | $cols | map($row[.] // "null")) as $rows | $cols, $rows[] | @csv' < test.json

(The whole string is pretty good explained here: https://stackoverflow.com/a/32965227/16174836)

ethergeist
  • 599
  • 4
  • 14
1

You can "stringify" the value using tostring by changing map($row[.]) into map($row[.]|tostring):

$ cat so2332.json
{
  "id": 1,
  "date": "2014-05-05T19:07:48.577"
}
{
  "id": 2,
  "date": null
}

$ jq --slurp --raw-output '(map(keys) | add | unique) as $cols | map(. as $row | $cols | map($row[.]|tostring)) as $rows | $cols, $rows[] | @csv' so2332.json
"date","id"
"2014-05-05T19:07:48.577","1"
"null","2"

Note that the use of tostring will cause the numbers to be converted to strings.

Joe Casadonte
  • 15,888
  • 11
  • 45
  • 57
  • As is evident from the example, using `tostring` for all the keys causes the `id` to become a CSV string, which may be undesirable. – peak Jun 19 '21 at 17:50
  • Indeed -- I mentioned that in a comment on your answer, but didn't mention it here. I will add a note. – Joe Casadonte Jun 19 '21 at 19:35