jq:对象不能为csv格式，只能为数组 [英] jq: Object cannot be csv-formatted, only array

问题描述

我是jq的新手，我有一个DynamoDB表中的JSON文件，我想将其转换为CSV.这是我的JSON文件.

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

但是如果我跑步

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

然后我得到一个错误:

jq:错误(在test.json:22处):对象({"SnsPublis ...)不能为CSV格式，只能是数组

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

如何将该JSON正确转换为CSV?我尝试了一个小时的谷歌搜索，但似乎无法解决.

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.

谢谢！

推荐答案

这是一个通用的JSON到CSV转换器，仅进行一个主要假设和一个次要假设.

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

主要假设是所有JSON实体都是保形的.在您的情况下，这意味着所有对应的对象都具有相同的键(尽管它们的顺序可能不同).如果违反了此假设，则会引发错误条件，并停止处理.

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.

次要假设是键名不包含点(.")；如果任何键名确实包含点，那么某些标头名称可能很难读取或解析，因为标头是通过使用点作为join字符来形成的.如果这是一个问题，那么您可能希望使用其他联接字符.

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.

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

调用

jq -rf json2csv.jq INPUT.json

输出

"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","[e-mail]","0100016c33f91857-600a8e44-c419-4a02-bfd6-7f6908f5969e-000000","[{""emailAddress"":""[e-mail]"",""action"":""failed"",""status"":""5.1.1"",""diagnosticCode"":""smtp; 550 5.1.1 user unknown""}]"

变化:读取JSON流

使用上述基础结构，还可以轻松地将带标头的共形JSON实体流转换为CSV格式.

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

插图表明相应对象中的键不必具有相同的顺序

[ {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

产生:

"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)];

这篇关于jq:对象不能为csv格式，只能为数组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！