gpt4 book ai didi

sql - Bigquery - json_extract 从数组中提取所有元素

转载 作者:行者123 更新时间:2023-12-03 14:45:58 28 4
gpt4 key购买 nike

我正在尝试从一组 jsons 中的每个 json 中提取两个 key (使用 sql legacy)
目前我正在使用 json 提取功能:

json_extract(json_column , '$[1].X') AS X,
json_extract(json_column , '$[1].Y') AS Y,

我怎样才能让它在'json arry column'的每个json上运行,而不仅仅是[1](例如)?

一个示例 json:
[

{"blabla":000,"X":1,"blabla":000,"blabla":000,"blabla":000,,"Y":"2"},

{"blabla":000,"X":3,"blabla":000,"blabla":000,"blabla":000,,"Y":"4"},

]

提前致谢!

最佳答案

2020 年更新:JSON_EXTRACT_ARRAY()

BigQuery 现在支持 JSON_EXTRACT_ARRAY() :

  • https://cloud.google.com/bigquery/docs/reference/standard-sql/json_functions#json_extract_array

  • 例如,要解决这个特定问题:

    SELECT id
    , ARRAY(
    SELECT JSON_EXTRACT_SCALAR(x, '$.author.email')
    FROM UNNEST(JSON_EXTRACT_ARRAY(payload, "$.commits"))x
    ) emails
    FROM `githubarchive.day.20180830`
    WHERE type='PushEvent'
    AND id='8188163772'

    enter image description here

    上一个答案

    让我们从一个类似的问题开始——这不是从 json 数组中提取所有电子邮件的非常方便的方法:
    SELECT id
    , [ JSON_EXTRACT_SCALAR(JSON_EXTRACT(payload, '$.commits'), '$[0].author.email')
    , JSON_EXTRACT_SCALAR(JSON_EXTRACT(payload, '$.commits'), '$[1].author.email')
    , JSON_EXTRACT_SCALAR(JSON_EXTRACT(payload, '$.commits'), '$[2].author.email')
    , JSON_EXTRACT_SCALAR(JSON_EXTRACT(payload, '$.commits'), '$[3].author.email')
    ] emails
    FROM `githubarchive.day.20180830`
    WHERE type='PushEvent'
    AND id='8188163772'

    enter image description here

    我们现在处理这个问题的最好方法是在 UDF 中使用一些 JavaScript 将 json 数组拆分为 SQL 数组:
    CREATE TEMP FUNCTION json2array(json STRING)
    RETURNS ARRAY<STRING>
    LANGUAGE js AS """
    return JSON.parse(json).map(x=>JSON.stringify(x));
    """;

    SELECT * EXCEPT(array_commits),
    ARRAY(SELECT JSON_EXTRACT_SCALAR(x, '$.author.email') FROM UNNEST(array_commits) x) emails
    FROM (
    SELECT id
    , json2array(JSON_EXTRACT(payload, '$.commits')) array_commits
    FROM `githubarchive.day.20180830`
    WHERE type='PushEvent'
    AND id='8188163772'
    )

    enter image description here

    关于sql - Bigquery - json_extract 从数组中提取所有元素,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/52120182/

    28 4 0
    Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
    广告合作:1813099741@qq.com 6ren.com