如何使用BigQuery提取JSON对象中的第一个键值对

0vvn1miw  于 2023-10-21  发布在  其他
关注(0)|答案(1)|浏览(129)

我尝试使用BigQuery提取JSON对象中某个键的值。但是,对于表中的每条记录,键都是唯一的,如图所示。Keys and values
我的JSON对象的结构是这样的:Object's structure
(Note:我正在尝试解嵌套作为此唯一键的值的regularHours数组。)
我尝试使用-“SELECT JSON_EXTRACT(response.data,'$. menus')FROM MyDataSet.UberEats”这只给我键和它的值,但我只是在取消嵌套这个键的数组值之后,我不知道如何进一步进行。(注:响应是字段名称)

iqih9akk

iqih9akk1#

请尝试以下UDF转换Dictionary对象,因为我们无法在静态类型的SQL语句中引用Dynamic键

CREATE TEMPORARY FUNCTION dictToArray(input JSON)
RETURNS JSON
LANGUAGE js AS """
  return Object.entries(input).map(([id, values]) => ({ id, values }));
""";

-- Direct Function
SELECT
  dictToArray(response)
FROM (SELECT JSON '{ "ID1": [10,11,12,13], "ID2": [20,21,22,23,24], "ID3": [31,32,33,34] }' AS response

Output
------
[{"id":"ID1","values":[10,11,12,13]},{"id":"ID2","values":[20,21,22,23,24]},{"id":"ID3","values":[31,32,33,34]}]

--Unnest Operation with Bigquery Native Types
SELECT
    SAFE.STRING(item.id) AS id,
    SAFE.INT64(value) AS values
FROM (SELECT JSON '{ "ID1": [10,11,12,13], "ID2": [20,21,22,23,24], "ID3": [31,32,33,34] }' AS response)
     LEFT JOIN UNNEST(JSON_EXTRACT_ARRAY(dictToArray(response), '$')) AS item
     LEFT JOIN UNNEST(JSON_EXTRACT_ARRAY(item.values)) AS value

/*----+--------*
| id  | values |
+-----+--------|
| ID1 | 10     |    
| ID1 | 11     |
| ID1 | 12     |
| ID1 | 13     |
| ID2 | 20     |
| ID2 | 21     |
| ID2 | 22     |
| ID2 | 23     |
| ID2 | 24     |
| ID3 | 31     |
| ID3 | 32     |
| ID3 | 33     |
| ID3 | 34     |
+-----+-------*/

相关问题