使用 JQ 将 JSON blob 转换为 BQ 友好格式

问题描述

坦率地说，我几乎没有使用 JSON、JQ 或 Java 方面的任何经验.我花了很多时间尝试使用 jq 命令行函数以一种可以轻松输入 Google BigQuery 的方式正确格式化测试数据块.

{total_items":848，page_count":34，项目": [{"landing_id": "708d9e3eb106820f98162d879198774b",令牌":708d9e3eb106820f98162d879198774b"，"response_id": "708d9e3eb106820f98162d879198774b","landed_at": "2019-02-12T01:58:02Z","submitted_at": "2019-02-12T01:58:31Z",元数据":{"user_agent": "Mozilla/5.0 (Windows NT 6.1; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/72.0.3560.98 Safari/537.36","平台": "其他","referer": "https://test.typeform.com/to/LTYE9W?prefill_answer=8&[email protected]","network_id": "35b9eae170",浏览器":默认"},答案":[{场地": {"id": "fX64BkjuxYy1","type": "opinion_scale",参考":97f8e18ad06a02e6"},"类型": "数量",数量":8},{场地": {"id": "lYeFxbL67g8B","type": "multiple_choice",参考":78d09e15-7d42-49ec-9f9d-004bf7d3058a"},类型":选择"，选择":{标签": [经验"]}},{场地": {"id": "D3ubKSVfNnlY","type": "multiple_choice",参考":684cb3bd-09cb-4f27-8e7d-baef6a09f787"},类型":选择"，选择":{标签": [状况"]}},{场地": {"id": "UccviSuUQPio","type": "yes_no",参考":ed7e0d9c-5b62-4b0f-9395-54a53d125711"},"类型": "布尔值",布尔值":假}],隐藏":{电子邮件":[email protected]"}}]}

请注意，用户peak"的答案缺少 -c 和 [] 以控制输出并从数组中拆分为不同的项目.

To be upfront, I have next to no experience with JSON, JQ, or much of anything on the Java side. I've been spending a lot of time trying to use the jq command line function to properly format a test blob of data in a way that I can easily feed into Google BigQuery.

{
    "total_items": 848,
    "page_count": 34,
    "items": [
        {
            "landing_id": "708d9e3eb106820f98162d879198774b",
            "token": "708d9e3eb106820f98162d879198774b",
            "response_id": "708d9e3eb106820f98162d879198774b",
            "landed_at": "2019-02-12T01:58:02Z",
            "submitted_at": "2019-02-12T01:58:31Z",
            "metadata": {
                "user_agent": "Mozilla/5.0 (Windows NT 6.1; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/72.0.3560.98 Safari/537.36",
                "platform": "other",
                "referer": "https://test.typeform.com/to/LTYE9W?prefilled_answer=8&[email protected]",
                "network_id": "35b9eae170",
                "browser": "default"
            },
            "answers": [
                {
                    "field": {
                        "id": "fX64BkjuxYy1",
                        "type": "opinion_scale",
                        "ref": "97f8e18ad06a02e6"
                    },
                    "type": "number",
                    "number": 8
                },
                {
                    "field": {
                        "id": "lYeFxbL67g8B",
                        "type": "multiple_choice",
                        "ref": "78d09e15-7d42-49ec-9f9d-004bf7d3058a"
                    },
                    "type": "choices",
                    "choices": {
                        "labels": [
                            "Experience"
                        ]
                    }
                },
                {
                    "field": {
                        "id": "D3ubKSVfNnlY",
                        "type": "multiple_choice",
                        "ref": "684cb3bd-09cb-4f27-8e7d-baef6a09f787"
                    },
                    "type": "choices",
                    "choices": {
                        "labels": [
                            "Condition"
                        ]
                    }
                },
                {
                    "field": {
                        "id": "UccviSuUQPio",
                        "type": "yes_no",
                        "ref": "ed7e0d9c-5b62-4b0f-9395-54a53d125711"
                    },
                    "type": "boolean",
                    "boolean": false
                }
            ],
            "hidden": {
                "email": "[email protected]"
            }
        }
        ]
}

I've been using this tutorial, but with no success, and it's getting incredibly frustrating

Let's assume I want all fields, but I want to get rid of that top part that includes total_items and page_count. So essentially, everything beginning with landing_id. I apologize for not going further into my previous attempts to give you all a baseline, but I just haven't gotten anywhere.

解决方案

For the given example:

 jq -c  .items[] lala.json  > lala.jq.json

Then you can load into BigQuery:

 bq load --source_format NEWLINE_DELIMITED_JSON --autodetect fh-bigquery:deleting.testjson lala.jq.json

And then it's ready to be queried:

Note that the answer from user "peak" is missing -c and [] to control the output and splitting into different items from the array.

这篇关于使用 JQ 将 JSON blob 转换为 BQ 友好格式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！