BigQuery提取含动态键的嵌套JSON异常求助：返回[object Object]

问题 #

需要提取带动态键的嵌套JSON，已成功提取键，但解析值时得到[object Object]的异常结果。希望用BigQuery内置函数解决，尽量不使用自定义JS UDF。

输入示例 #

{
    "key1":{"ItemID":1,"UseCount":4,"ItemCount":7},
    "key2":{"ItemID":2,"UseCount":5,"ItemCount":8},
    "key3":{"ItemID":3,"UseCount":6,"ItemCount":9}
}

当前查询（使用bigquery-utils的UDF） #

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)
;

当前问题 #

value列返回[object Object]，无法得到正确的JSON对象字符串。

期望结果 #

JSON_STRING1  |  "key1"   |  {"ItemID":1,"UseCount":4,"ItemCount":7}
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：用内置函数直接提取（无需额外UDF） #

先提取所有键，再通过JSON_QUERY结合动态键定位对应的值：

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}}'
),
extracted_keys AS (
    SELECT 
        json_string,
        key
    FROM sample_logs
    CROSS JOIN UNNEST(bqutil.fn.json_extract_keys(json_string)) as key
)
SELECT
    json_string,
    key,
    JSON_QUERY(json_string, CONCAT('$."', key, '"')) as value
FROM extracted_keys;

方法2：用OBJECT_TO_ARRAY一步转换（BigQuery 2023+） #

如果使用新版本BigQuery，直接用内置函数将JSON对象转为键值对数组，无需额外UDF：

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,
    kv[OFFSET(0)] as key,
    kv[OFFSET(1)] as value
FROM sample_logs
CROSS JOIN UNNEST(OBJECT_TO_ARRAY(JSON_PARSE(json_string))) as kv;

这个方法最简洁，直接把JSON对象拆成键值对数组，展开后就能得到目标结果。

方法3：修复现有UDF的返回格式 #

如果一定要用json_extract_values，问题在于该UDF返回的是JSON对象而非字符串，导致TO_JSON_STRING无法正确序列化。修改UDF逻辑，让它返回JSON.stringify(value)，这样提取的值就是字符串格式，无需额外转换。

内容的提问来源于stack exchange，提问作者Jiho Choi