json - jq: Object cannot be csv-formatted, only array

Question

I am new to jq and I have a JSON file from a DynamoDB table which I want to convert to CSV. This is my JSON file.

[
    {
        "SnsPublishTime": {
            "S": "2019-07-27T15:07:38.904Z"
        },
        "SESreportingMTA": {
            "S": "dsn; a8-19.smtp-out.amazonses.com"
        },
        "SESMessageType": {
            "S": "Bounce"
        },
        "SESDestinationAddress": {
            "S": "bounce@simulator.amazonses.com"
        },
        "SESMessageId": {
            "S": "0100016c33f91857-600a8e44-c419-4a02-bfd6-7f6908f5969e-000000"
        },
        "SESbounceSummary": {
            "S": "[{"emailAddress":"bounce@simulator.amazonses.com","action":"failed","status":"5.1.1","diagnosticCode":"smtp; 550 5.1.1 user unknown"}]"
        }
    }
]

I get the correct output if I run

jq -r '.[] ' test.json

but if I run

jq -r '.[] |@csv' test.json

Then I am getting an error:

jq: error (at test.json:22): object ({"SnsPublis...) cannot be csv-formatted, only array

How can I convert this JSON to a CSV properly? I tried googling for over an hour and can't seem to be able to figure it out.

Thank you!

Answer 1

Here is a generic JSON-to-CSV converter that makes just one major assumption and one minor assumption.

The major assumption is that all the JSON entities are conformal. In your case, it means that all corresponding objects have the same keys (though they may be in different order). If this assumption is ever violated, an error condition is raised, and processing stops.

The minor assumption is that key names do not contain a dot ("."); if any key name does contain a dot, then some of the header names might be difficult to read or parse, since the headers are formed by using the dot as the join character. If that is a problem, then you might wish to use a different join character.

The jq program

def json2header:
  [paths(scalars)];

def json2array($header):
  json2header as $h
  | if $h == $header or (($h|sort) == ($header|sort))
    then [$header[] as $p | getpath($p)]
    else "headers do not match: expected followed by found paths:" | debug
    | ($header|map(join(".")) | debug)
    | ($h|map(join(".")) | debug)
    | "headers do not match" | error
    end ;

# given an array of conformal objects, produce "CSV" rows, with a header row:
def json2csv:
  (.[0] | json2header) as $h
  | ([$h[]|join(".")], (.[] | json2array($h))) 
  | @csv ;

# `main`
json2csv

The invocation

jq -rf json2csv.jq INPUT.json

The output

"SnsPublishTime.S","SESreportingMTA.S","SESMessageType.S","SESDestinationAddress.S","SESMessageId.S","SESbounceSummary.S"
"2019-07-27T15:07:38.904Z","dsn; a8-19.smtp-out.amazonses.com","Bounce","bounce@simulator.amazonses.com","0100016c33f91857-600a8e44-c419-4a02-bfd6-7f6908f5969e-000000","[{""emailAddress"":""bounce@simulator.amazonses.com"",""action"":""failed"",""status"":""5.1.1"",""diagnosticCode"":""smtp; 550 5.1.1 user unknown""}]"

Variation: reading a JSON stream

With the above infrastructure, it is also easy to convert a stream of conformal JSON entities into the CSV format, with headers.

def inputs2csv:
  json2header as $h
  | [$h[]|join(".")],
    json2array($h),
    (inputs|json2array($h))
  | @csv ;

# `main`
inputs2csv

Illustration showing that keys in corresponding objects need not be in the same order

[ {a:1, b: {c:3, d: [{e:4},{e:5, f:6}]}},
  {b: {d: [{e:4},{f:6, e:5}], c:3}, a:1}
 ] 
| json2csv

produces:

"a","b.c","b.d.0.e","b.d.1.e","b.d.1.f"
1,3,4,5,6
1,3,4,5,6

Another variation

Under some circumstances, the checking for conformity might not be necessary, so you'd be left with:

def json2array($header):
  [$header[] as $p | getpath($p)];