无法在解析JSON字符串数据时使用Struct或Unnest [英] Not able to use Struct or Unnest in parsing JSON string data

问题描述

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

I am having hard time parsing data from BigQuery in column format for analysis, help is really appreciated.

我有具有以下架构的user_detail表

I have user_detail table with following schema

<身体>

字段名称	类型	模式	说明
user_id	INTEGER	NULLABLE
用户名	STRING	NULLABLE	{"dl":"512"}

此表中有以下示例数据

<身体>

user_id	user_names
12829	[{``_id'':{``$ oid'':``5d650676af82eb0a30737e74''}，``_type'':``UserName''，``capture_date'':{``$ date'':``''2019-08-27T00:00:00.000 + 0000"}"，来源":"google"，"name1":"John"，"name3":"Doe"，"name4":"D/O Jane Doe"，性别":"1"，"dob":{"$ date":"1986-07-10T00:00:00.000 + 0000"}}]

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

I want to get the data in following tabular format

选择user_id，源，名称1，名称3

我尝试了unnest运算符，但是显然因为user_names列是字符串格式，所以我无法获取它，因为它不是数组格式( FROM子句中引用的值必须是数组.user_names的类型为STRING，位于[5:10] )

I have tried unnest operator but apparently because user_names column is in string format, I am not able to get it as it is not in array format (Values referenced in FROM clause must be arrays. user_names has type STRING at [5:10])

您能帮我如何做到这一点吗?

Can you please help me how can I accomplish this?

推荐答案

以下查询是有关此操作的示例:

The following query is an example on how to do it:

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字符串是字典列表，因此在这种情况下，您必须选择要解析的元素第一个.

You have to use $[0].ATTRIBUTE_NAME because your JSON string is a list of dictionaries so you have to select the element to parse in this case the first one.

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

If you did not have the square brackets from the list you can just use $.ATTRIBUTE_NAME.

这篇关于无法在解析JSON字符串数据时使用Struct或Unnest的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！