使用jq将JSON转换为CSV [英] Convert JSON to CSV with jq

问题描述

我正在尝试使用jq在我的JSON文件中提取sids, ll, state, name, smry值并导出到csv.

I'm trying to extract the sids, ll, state, name, smry values in my JSON file using jq and export to a csv.

JSON文件(out.json):

{
    "data": [
        {
            "meta": {
                "uid": 74529,
                "ll": [
                    -66.9333,
                    47.0667
                ],
                "sids": [
                    "CA008102500 6"
                ],
                "state": "NB",
                "elev": 1250,
                "name": "LONG LAKE"
            },
            "smry": [
                [
                    "42",
                    "1955-02-23"
                ]
            ]
        },
        {
            "meta": {
                "uid": 74534,
                "ll": [
                    -67.2333,
                    45.9667
                ],
                "sids": [
                    "CA008103425 6"
                ],
                "state": "NB",
                "elev": 150.9,
                "name": "NACKAWIC"
            },
            "smry": [
                [
                    "40",
                    "1969-02-23"
                ]
            ]
        },
        {
            "meta": {
                "uid": 74549,
                "ll": [
                    -67.4667,
                    47.4667
                ],
                "sids": [
                    "CA008104933 6"
                ],
                "state": "NB",
                "elev": 794,
                "name": "ST QUENTIN"
            },
            "smry": [
                [
                    "M",
                    "M"
                ]
            ]
        },
        {
            "meta": {
                "uid": 74550,
                "ll": [
                    -67.2667,
                    45.1833
                ],
                "sids": [
                    "CA008104936 6"
                ],
                "state": "NB",
                "elev": 36.1,
                "name": "ST STEPHEN"
            },
            "smry": [
                [
                    "48",
                    "1900-02-23"
                ]
            ]
        },
        {
            "meta": {
                "uid": 74554,
                "ll": [
                    -67.25,
                    47.2667
                ],
                "sids": [
                    "CA008105000 6"
                ],
                "state": "NB",
                "elev": 915.4,
                "name": "SISSON DAM"
            },
            "smry": [
                [
                    "35",
                    "1955-02-23"
                ]
            ]
        }
    ]
}

终端代码:

jq '.data | [ {sids, ll, state, name, smry} ]' out.json

我遇到以下错误:

assertion "cb == jq_util_input_next_input_cb" failed: file "/usr/src/ports/jq/jq-1.5-3.x86_64/src/jq-1.5/util.c", line 371, function: jq_util_input_get_position
Aborted (core dumped)

示例预期输出:

sids, ll, state, name, smry
CA008102500, -66.9333, 47.0667, NB, LONG LAKE, 42,1955-02-23
CA008103425, -67.2333, 45.9667, NB, NACKAWIC, 35,1955-02-23

我在做什么错了?

推荐答案

这有点复杂，因为在展平整个记录之前，您需要展平sids，ll和smry.我建议创建一个jq文件:

It's a bit more complex because you need to flatten sids, ll and smry before you can flatten the whole record. I recommend to create a jq file:

foo.jq :

.data[]|{
    "sids":(.meta.sids[0]|split(" ")[0]),
    "ll":(.meta.ll|map(tostring)|join(",")),
    "state":.meta.state,
    "name":.meta.name,
    "smry":(.smry[]|join(","))
}|join(",")
# or, for robust csv output
# } | @csv 

然后致电:

jq -rf foo.jq file.json

输出:

CA008102500,-66.9333,47.0667,NB,LONG LAKE,42,1955-02-23
CA008103425,-67.2333,45.9667,NB,NACKAWIC,40,1969-02-23
CA008104933,-67.4667,47.4667,NB,ST QUENTIN,M,M
CA008104936,-67.2667,45.1833,NB,ST STEPHEN,48,1900-02-23
CA008105000,-67.25,47.2667,NB,SISSON DAM,35,1955-02-23

这篇关于使用jq将JSON转换为CSV的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！