json - 将 json 数据加载到谷歌大查询的架构

Question

我对我们正在做的项目有疑问...

我尝试将此 JSON 提取到 Google Big Query，但无法从 JSON 输入中获取 JSON 投票对象字段。我尝试了模式中的“记录”和“字符串”类型。

{
    "votes": {
        "funny": 10,
        "useful": 10,
        "cool": 10
    },
    "user_id": "OlMjqqzWZUv2-62CSqKq_A",
    "review_id": "LMy8UOKOeh0b9qrz-s1fQA",
    "stars": 4,
    "date": "2008-07-02",
    "text": "This is what this 4-star bar is all about.",
    "type": "review",
    "business_id": "81IjU5L-t-QQwsE38C63hQ"
}

此外，我无法从下面的 JSON 中获取类别和邻域 JSON 数组的表格？对于这些输入，我的模式应该是什么？不幸的是，在这种情况下，文档并没有太大帮助，或者我可能没有看对地方..

{
    "business_id": "Iu-oeVzv8ZgP18NIB0UMqg",
    "full_address": "3320 S Hill St\nSouth East LA\nLos Angeles, CA 90007",
    "schools": [
        "University of Southern California"
    ],
    "open": true,
    "categories": [
        "Medical Centers",
        "Health and Medical"
    ],
    "neighborhoods": [
        "South East LA"
    ]
}

我能够获得常规字段，但仅此而已......感谢任何帮助!

Answer 1

对于 business看来你希望学校是一个重复的领域。您的架构应该是:

"schema": {
    "fields": [
        {
            "name": "business_id",
            "type": "string"
        }.
        {
            "name": "full_address",
            "type": "string"
        },
        {
            "name": "schools",
            "type": "string",
            "mode": "repeated"
        },
        {
            "name": "open",
            "type": "boolean"
        }
    ]
}

对于 votes看来你想要记录。您的架构应该是:

"schema": {
    "fields": [
        {
            "name": "name",
            "type": "string"
        }.
        {
            "name": "votes",
            "type": "record",
            "fields": [
                {
                    "name": "funny",
                    "type": "integer",
                },
                {
                    "name": "useful",
                    "type": "integer"
                },
                {
                    "name": "cool",
                    "type": "integer"
                }
            ]
        },
    ]
}

Source