问题：
如何将嵌套json导入google big query
桂浩言
我正在将JSON插入Google Big Query中。问题的底部是JSON的模式。
下面是一个JSON示例：
{
"_index":"data",
"_type":"collection_v1",
"_id":"548d035f23r8987b768a5e60",
"_score":1,
"_source":{
"fullName":"Mike Smith",
"networks":[
{
"id":[
"12923449"
],
"network":"facebook",
"link":"https://www.facebook.com/127654449"
}
],
"sex":{
"network":"facebook",
"value":"male"
},
"interests":{
},
"score":1.045,
"merged_by":"548f899444v5t4v45te9a4cc"
}
}
如您所见，有一个“_source.fullname”字段带有“Mike Smith”。
当我试图用它创建表时，它出错了：
为非重复字段指定的数组:_source.fullname。
我相信这个字段是_source的一次性字段。我如何克服这个错误？
下面是模式：
[
{
"name": "_index",
"type": "STRING",
"mode": "NULLABLE"
},
{
"name": "_id",
"type": "STRING",
"mode": "NULLABLE"
},
{
"name": "_type",
"type": "STRING",
"mode": "NULLABLE"
},
{
"name": "score",
"type": "STRING",
"mode": "NULLABLE"
},
{
"name": "header",
"type": "STRING",
"mode": "NULLABLE"
},
{
"name": "fullName",
"type": "STRING",
"mode": "NULLABLE"
},
{
"name": "src",
"type": "STRING",
"mode": "NULLABLE"
},
{
"name": "avatar",
"type": "STRING",
"mode": "NULLABLE"
},
{
"name": "merged_by",
"type": "STRING",
"mode": "NULLABLE"
},
{
"name": "cover",
"type": "STRING",
"mode": "NULLABLE"
},
{
"name": "sex",
"type": "RECORD",
"mode": "NULLABLE",
"fields": [
{
"name": "network",
"type": "STRING",
"mode": "NULLABLE"
},
{
"name": "value",
"type": "STRING",
"mode": "NULLABLE"
}
]
},
{
"name": "_source",
"type": "RECORD",
"mode": "NULLABLE",
"fields": [
{
"name": "fullName",
"type": "STRING",
"mode": "NULLABLE"
},
{
"name": "links",
"type": "STRING",
"mode": "REPEATED"
},
{
"name": "birthday",
"type": "RECORD",
"mode": "REPEATED",
"fields": [
{
"name": "value",
"type": "STRING",
"mode": "NULLABLE"
},
{
"name": "network",
"type": "STRING",
"mode": "NULLABLE"
}
]
},
{
"name": "phones",
"type": "STRING",
"mode": "REPEATED"
},
{
"name": "pictures",
"type": "RECORD",
"mode": "REPEATED",
"fields": [
{
"name": "url",
"type": "STRING",
"mode": "NULLABLE"
},
{
"name": "tab",
"type": "STRING",
"mode": "NULLABLE"
},
{
"name": "network",
"type": "STRING",
"mode": "NULLABLE"
}
]
},
{
"name": "contacts",
"type": "RECORD",
"mode": "REPEATED",
"fields": [
{
"name": "id",
"type": "STRING",
"mode": "NULLABLE"
},
{
"name": "fullName",
"type": "STRING",
"mode": "NULLABLE"
},
{
"name": "tag",
"type": "STRING",
"mode": "NULLABLE"
},
{
"name": "network",
"type": "STRING",
"mode": "NULLABLE"
}
]
},
{
"name": "groups",
"type": "RECORD",
"mode": "REPEATED",
"fields": [
{
"name": "id",
"type": "STRING",
"mode": "NULLABLE"
},
{
"name": "Name",
"type": "STRING",
"mode": "NULLABLE"
},
{
"name": "network",
"type": "STRING",
"mode": "NULLABLE"
}
]
},
{
"name": "skills",
"type": "RECORD",
"mode": "REPEATED",
"fields": [
{
"name": "value",
"type": "STRING",
"mode": "NULLABLE"
},
{
"name": "network",
"type": "STRING",
"mode": "NULLABLE"
}
]
},
{
"name": "relations",
"type": "RECORD",
"mode": "REPEATED",
"fields": [
{
"name": "value",
"type": "STRING",
"mode": "NULLABLE"
},
{
"name": "network",
"type": "STRING",
"mode": "NULLABLE"
}
]
},
{
"name": "about",
"type": "RECORD",
"mode": "REPEATED",
"fields": [
{
"name": "value",
"type": "STRING",
"mode": "NULLABLE"
},
{
"name": "network",
"type": "STRING",
"mode": "NULLABLE"
}
]
},
{
"name": "emails",
"type": "STRING",
"mode": "REPEATED"
},
{
"name": "languages",
"type": "STRING",
"mode": "REPEATED"
},
{
"name": "places",
"type": "RECORD",
"mode": "REPEATED",
"fields": [
{
"name": "network",
"type": "STRING",
"mode": "NULLABLE"
},
{
"name": "value",
"type": "STRING",
"mode": "NULLABLE"
},
{
"name": "type",
"type": "STRING",
"mode": "NULLABLE"
}
]
},
{
"name": "education",
"type": "RECORD",
"mode": "REPEATED",
"fields": [
{
"name": "network",
"type": "STRING",
"mode": "NULLABLE"
},
{
"name": "school",
"type": "STRING",
"mode": "NULLABLE"
}
]
},
{
"name": "experience",
"type": "RECORD",
"mode": "REPEATED",
"fields": [
{
"name": "network",
"type": "STRING",
"mode": "NULLABLE"
},
{
"name": "start",
"type": "NUMERIC",
"mode": "NULLABLE"
},
{
"name": "company",
"type": "STRING",
"mode": "NULLABLE"
},
{
"name": "title",
"type": "STRING",
"mode": "NULLABLE"
}
]
},
{
"name": "networks",
"type": "RECORD",
"mode": "REPEATED",
"fields": [
{
"name": "network",
"type": "STRING",
"mode": "NULLABLE"
},
{
"name": "link",
"type": "STRING",
"mode": "NULLABLE"
},
{
"name": "id",
"type": "STRING",
"mode": "REPEATED"
}
]
},
{
"name": "network",
"type": "RECORD",
"mode": "REPEATED",
"fields": [
{
"name": "others",
"type": "RECORD",
"mode": "REPEATED",
"fields": [
{
"name": "network",
"type": "STRING",
"mode": "NULLABLE"
},
{
"name": "value",
"type": "STRING",
"mode": "NULLABLE"
},
{
"name": "tag",
"type": "STRING",
"mode": "NULLABLE"
}
]
},
{
"name": "books",
"type": "RECORD",
"mode": "REPEATED",
"fields": [
{
"name": "network",
"type": "STRING",
"mode": "NULLABLE"
},
{
"name": "value",
"type": "STRING",
"mode": "NULLABLE"
},
{
"name": "tag",
"type": "STRING",
"mode": "NULLABLE"
}
]
},
{
"name": "music",
"type": "RECORD",
"mode": "REPEATED",
"fields": [
{
"name": "network",
"type": "STRING",
"mode": "NULLABLE"
},
{
"name": "value",
"type": "STRING",
"mode": "NULLABLE"
},
{
"name": "tag",
"type": "STRING",
"mode": "NULLABLE"
}
]
},
{
"name": "games",
"type": "RECORD",
"mode": "REPEATED",
"fields": [
{
"name": "network",
"type": "STRING",
"mode": "NULLABLE"
},
{
"name": "value",
"type": "STRING",
"mode": "NULLABLE"
},
{
"name": "tag",
"type": "STRING",
"mode": "NULLABLE"
}
]
},
{
"name": "spotify",
"type": "RECORD",
"mode": "REPEATED",
"fields": [
{
"name": "network",
"type": "STRING",
"mode": "NULLABLE"
},
{
"name": "value",
"type": "STRING",
"mode": "NULLABLE"
},
{
"name": "tag",
"type": "STRING",
"mode": "NULLABLE"
}
]
}
]
}
]
}
]
共有1个答案
耿俊
您可以导入完整的json行，就像它是一个CSV一样--基本上是一列json对象的BigQuery表。然后您可以在BigQuery内部随意解析JSON，查询如下所示：
WITH j AS (
SELECT """{"_index":"data","_type":"collection_v1","_id":"548d035f23r8987b768a5e60","_score":1,"_source":{"fullName":"Mike Smith","networks":[{"id":["12923449"],"network":"facebook","link":"https://www.facebook.com/127654449"}],"sex":{"network":"facebook","value":"male"},"interests":{},"score":1.045,"merged_by":"548f899444v5t4v45te9a4cc"}}""" j
)
SELECT index
, STRUCT(
JSON_EXTRACT_SCALAR(source, '$.fullName') AS fullName
, [
STRUCT(
JSON_EXTRACT_SCALAR(source, '$.networks[0].id[0]') AS id
, JSON_EXTRACT_SCALAR(source, '$.networks[0].network') AS network
, JSON_EXTRACT_SCALAR(source, '$.networks[0].link') AS link)
] AS networks
) source
FROM (
SELECT JSON_EXTRACT_SCALAR(j.j, '$._index') index
, JSON_EXTRACT(j.j, '$._source') source
FROM j
)
参见：
- https://medium.com/google-cloud/bigquery-lazy-data-loading-ddl-dml-partitions-and-half-a-trillion-wikipedia-pageviews-CD3eACD657B6
类似资料：
-
问题内容： 我有大量（〜40000）嵌套的JSON对象，我想将它们插入elasticsearch中。 JSON对象的结构如下： 因此，JSON字段（此示例中为地址）可以具有JSON对象数组。 Logstash配置看起来像什么将这样的JSON文件/对象导入elasticsearch？该索引的elasticsearch映射应该看起来像JSON的结构。elasticsearch文档ID应设置为。 问
-
问题内容： 我有嵌套的JSON对象，例如 而且我需要获取_events数组并对其进行解析。但是我不知道_events之前的单元格中的内容以及它们的状态。如何使用这种结构？ 问题答案： 就像这样使用它： 这是一个有效的jsFiddle：http : //jsfiddle.net/ErHng/（ 注意 ：它会输出到控制台，因此您需要/ 在chrome中或在Firefox中打开firebug，然后重新运
-
我目前正在使用Firebase实时数据库。我已经将JSON导入实时数据库，但由于查询的限制，我需要打开Firebase Firestore。 我想将JSON导入Firebase的云Firestore。
-
问题内容： 例如，我有一个文件，它是一个对象数组（严格地形成），它很简单（没有嵌套的对象），就像这样（重要的是：它已经包含了id）： 我想将它们全部导入到表的postgres db中。 当我将其作为json类型的列导入到像这样的表中并用其中列出的对象命名的列，然后使用sql获取这些值并将其插入到真实表中时，我发现了一些非常困难的方法。 但是有没有一种简单的方法就可以将json导入到postgres
-
输入 JSON ： 预期输出JSON： 目前，我正在使用JOLTtransformJSON处理器和JOLT规范： 但我得到的输出要么是NULL，要么是原始JSON（带有差异规范）。提前感谢。