时间戳

首页 > 解决方案 > 在解析 JSON 字符串数据时无法使用 Struct 或 Unnest

google-bigquery - 在解析 JSON 字符串数据时无法使用 Struct 或 Unnest

问题描述

我很难以列格式解析来自 BigQuery 的数据以进行分析，非常感谢您的帮助。

我有具有以下架构的 user_detail 表

字段名称 类型 模式 描述
用户身份 整数 可空的
用户名 细绳 可空的 {"dl":"512"}

我在此表中有以下示例数据

用户身份 用户名
12829 [{"_id":{"$oid":"5d650676af82eb0a30737e74"},"_type":"UserName","capture_date":{"$date":"2019-08-27T00:00:00.000+0000"}, "source":"google","name1":"John","name3":"Doe","name4":"D/O Jane Doe","gender":"1","dob":{" $日期":"1986-07-10T00:00:00.000+0000"}}]

我想以以下表格格式获取数据

选择用户 ID、来源、名称 1、名称 3

我尝试过 unnest 运算符，但显然因为 user_names 列是字符串格式，我无法得到它，因为它不是数组格式（FROM 子句中引用的值必须是数组。user_names 在 [5:10] 处具有类型 STRING）

你能帮我怎么做吗？

标签： google-bigquery

解决方案

以下查询是有关如何执行此操作的示例：

with cte as (
    select 12829 as user_id, '[{"_id":{"$oid":"5d650676af82eb0a30737e74"},"_type":"UserName","capture_date":{"$date":"2019-08-27T00:00:00.000+0000"},"source":"google","name1":"John","name3":"Doe","name4":"D/O Jane Doe","gender":"1","dob":{"$date":"1986-07-10T00:00:00.000+0000"}}]' as json_line
)
select
    user_id,
    json_value(json_line, '$[0].source') as source,
    json_value(json_line, '$[0].name1') as name1,
    json_value(json_line, '$[0].name3') as name3
from cte

您必须使用$[0].ATTRIBUTE_NAME，因为您的 JSON 字符串是字典列表，因此您必须选择要解析的元素，在这种情况下是第一个。

如果您没有列表中的方括号，您可以使用$.ATTRIBUTE_NAME.

推荐阅读