BigQuery 提取具有动态键和值的嵌套 JSON

huangapple go评论59阅读模式
英文:

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]"
我目前能够借助 12 提取键,但解析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

BigQuery 提取具有动态键和值的嵌套 JSON

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

BigQuery 提取具有动态键和值的嵌套 JSON

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

BigQuery 提取具有动态键和值的嵌套 JSON

huangapple
  • 本文由 发表于 2023年5月10日 16:31:35
  • 转载请务必保留本文链接:https://go.coder-hub.com/76216389.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定