使用JQ将JSON blob转换为BQ友好格式 [英] Turning JSON blob into BQ friendly format with JQ

问题描述

首先，我几乎没有JSON，JQ或Java方面的任何经验.我已经花了很多时间尝试使用jq命令行功能来正确格式化测试数据块，以便我可以轻松地将其输入Google BigQuery.

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=[e-mail]",
                "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": "[e-mail]"
            }
        }
        ]
}

我一直在使用本教程，但没有成功，而且令人沮丧的是，

让我们假设我想要所有字段，但是我想摆脱包含total_items和page_count的顶部.因此，基本上所有内容均以landing_id开头.很抱歉，我没有进一步尝试为您提供所有基准，但是我什么都没得到.

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.

推荐答案

对于给定的示例:

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

然后您可以加载到BigQuery中:

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:

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

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友好格式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！