Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
1.8k views
in Technique[技术] by (71.8m points)

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

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": "[email protected]"
        },
        "SESMessageId": {
            "S": "0100016c33f91857-600a8e44-c419-4a02-bfd6-7f6908f5969e-000000"
        },
        "SESbounceSummary": {
            "S": "[{"emailAddress":"[email protected]","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!

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

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","[email protected]","0100016c33f91857-600a8e44-c419-4a02-bfd6-7f6908f5969e-000000","[{""emailAddress"":""[email protected]"",""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)];

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...