英文:
BigQuery extract nested JSON with dynamic keys and values
问题
I would like to extract nested JSON with dynamic keys.
我想提取具有动态键的嵌套JSON。
I am currently able to extract keys with the aid of 1
and 2
, but parsing the value of JSON object is producing unexpected results. "[object Object]"
我目前能够借助 1
和 2
提取键,但解析JSON对象的值会产生意外结果。 "[object Object]"
What would be the proper way of parsing the nested JSON with dynamic keys and values?
用什么方法来正确解析具有动态键和值的嵌套JSON呢?
(I would prefer not to use a custom JS UDF but I am not sure if existing JSON functions can handle the problem.)
(我希望不使用自定义JS UDF,但我不确定现有的JSON函数是否能处理这个问题。)
Input column of the record
{
"key1":{"ItemID":1,"UseCount":4,"ItemCount":7},
"key2":{"ItemID":2,"UseCount":5,"ItemCount":8},
"key3":{"ItemID":3,"UseCount":6,"ItemCount":9}
...
}
Current Query
bigquery-utils
:
json_extract_keys()
json_extract_values()
WITH
sample_logs AS (
SELECT '{"key1":{"ItemID":1,"UseCount":4,"ItemCount":7},"key2":{"ItemID":2,"UseCount":5,"ItemCount":8},"key3":{"ItemID":3,"UseCount":6,"ItemCount":9}}' as json_string,
UNION ALL SELECT '{"key4":{"ItemID":1,"UseCount":4,"ItemCount":7},"key5":{"ItemID":2,"UseCount":5,"ItemCount":8}}'
)
SELECT
json_string,
key,
TO_JSON_STRING(value) as value,
FROM sample_logs
CROSS JOIN UNNEST(bqutil.fn.json_extract_keys(json_string)) as key WITH OFFSET
INNER JOIN UNNEST(bqutil.fn.json_extract_values(json_string)) as value WITH OFFSET USING (OFFSET)
;
Results
Expected Results
JSON_STRING1 | "key1" | {"ItemID":1,"UseCount":4,"ItemCount":7} -- <- not [object Object]
JSON_STRING1 | "key2" | {"ItemID":2,"UseCount":5,"ItemCount":8}
JSON_STRING1 | "key3" | {"ItemID":3,"UseCount":6,"ItemCount":9}
JSON_STRING2 | "key4" | {"ItemID":1,"UseCount":4,"ItemCount":7}
JSON_STRING2 | "key5" | {"ItemID":2,"UseCount":5,"ItemCount":8}
英文:
I would like to extract nested JSON with dynamic keys.
I am currently able to extract keys with the aid of 1
and 2
, but parsing the value of JSON object is producing unexpected results. "[object Object]"
What would be the proper way of parsing the nested JSON with dynamic keys and values?
(I would prefer not to use a custom JS UDF but I am not sure if existing JSON functions can handle the problem.)
Input column of the record
{
"key1":{"ItemID":1,"UseCount":4,"ItemCount":7},
"key2":{"ItemID":2,"UseCount":5,"ItemCount":8},
"key3":{"ItemID":3,"UseCount":6,"ItemCount":9}
...
}
Current Query
bigquery-utils
:
json_extract_keys()
json_extract_values()
WITH
sample_logs AS (
SELECT '{"key1":{"ItemID":1,"UseCount":4,"ItemCount":7},"key2":{"ItemID":2,"UseCount":5,"ItemCount":8},"key3":{"ItemID":3,"UseCount":6,"ItemCount":9}}' as json_string,
UNION ALL SELECT '{"key4":{"ItemID":1,"UseCount":4,"ItemCount":7},"key5":{"ItemID":2,"UseCount":5,"ItemCount":8}}'
)
SELECT
json_string,
key,
TO_JSON_STRING(value) as value,
FROM sample_logs
CROSS JOIN UNNEST(bqutil.fn.json_extract_keys(json_string)) as key WITH OFFSET
INNER JOIN UNNEST(bqutil.fn.json_extract_values(json_string)) as value WITH OFFSET USING (OFFSET)
;
Results
Expected Results
JSON_STRING1 | "key1" | {"ItemID":1,"UseCount":4,"ItemCount":7} -- <- not [object Object]
JSON_STRING1 | "key2" | {"ItemID":2,"UseCount":5,"ItemCount":8}
JSON_STRING1 | "key3" | {"ItemID":3,"UseCount":6,"ItemCount":9}
JSON_STRING2 | "key4" | {"ItemID":1,"UseCount":4,"ItemCount":7}
JSON_STRING2 | "key5" | {"ItemID":2,"UseCount":5,"ItemCount":8}
答案1
得分: 3
以下是您要翻译的内容:
"JSON函数"要求jsonpath为字符串文字,而"JSON类型"允许对象键是变量。
"查询结果"
英文:
You can consider below.
WITH
sample_logs AS (
SELECT '{"key1":{"ItemID":1,"UseCount":4,"ItemCount":7},"key2":{"ItemID":2,"UseCount":5,"ItemCount":8},"key3":{"ItemID":3,"UseCount":6,"ItemCount":9}}' as json_string,
UNION ALL SELECT '{"key4":{"ItemID":1,"UseCount":4,"ItemCount":7},"key5":{"ItemID":2,"UseCount":5,"ItemCount":8}}'
)
SELECT json_string,
key,
PARSE_JSON(json_string)[key] value
FROM sample_logs,
UNNEST(bqutil.fn.json_extract_keys(json_string)) as key;
- JSON function requires a jsonpath to be a string literal, whereas JSON type allows an object key to be a variable.
Query result
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论