在Google Big Query中从JSON格式提取数据

e0uiprwp 于 2023-05-30 发布在 Go

关注(0)|答案(1)|浏览(205)

我试图从我的电源BI的JSON格式列中提取数据，我使用的数据源'谷歌大查询'在后端
我在data列中有一个JSON格式，它的格式如下

ARRAY<STRUCT<
  id STRING,
  status STRING,
  name ARRAY<STRUCT<code STRING, value STRING>>,
  description ARRAY<STRUCT<code STRING, value STRING>>
>>

我需要从data列中提取数据，该列将采用以下格式，该列应称为data.name

ARRAY<STRUCT<code STRING, value STRING>>

请救救我！
先谢谢你了

(SELECT MAX(name.value) FROM UNNEST(c.data_) AS data
cross join UNNEST(data.name) as name
where name.code in ('en')) as data

我尝试了像上面，但它不正确地给出输出

JSON

来源：https://stackoverflow.com/questions/76354597/extracting-data-from-json-format-in-google-big-query

1条答案

按热度按时间

pvcm50d11#

在使用unnest之前需要解析JSON：

WITH test_tbl as (
  Select 
    1 as colA, 
    JSON '''
      [{"id":"id1","status":"done",
        "name":[{"code":"c5","value":"5"},{"code":"c7","value":"7"}]},
      {"id":"id2",
        "name":[{"value":"9"}]} ]
      ''' as data
),
tbl as (
SELECT *,
json_extract_scalar(data_unnest.id) as id,
json_extract_scalar(data_name_unnest.value) as output_name
 from test_tbl
,unnest(json_extract_array(data)) as data_unnest,
unnest(json_extract_array(data_unnest.name)) as data_name_unnest
)

Select *,
max(output_name)  over () as output_name_max
from tbl~~~

赞(0）回复(0）举报 2023-05-30

我来回答

在Google Big Query中从JSON格式提取数据

1条答案

相关问题

热门标签

最新问答