在大查询中以动态的方式将json分为列

Question

我有以下JSON：

{
    "rewards": {
        "reward_1": {
            "type": "type 1",
            "amount": "amount 1"
        },
        "reward_2": {
            "type": "type 2",
            "amount": "amount 2"
        },
        "reward_3": {
            "type": "type 3",
            "amount": "amount 3"
        },
        "reward_4": {
            "type": "type 4",
            "amount": "amount 4"
        }
    }
}

这个JSON是动态的，我不一定知道它会得到多少奖励，这里是4个，但可以是2或8等。

我想在大查询中编写一个查询，以解析这些值动态地不知道其中有多少，然后将它们分成列，例如：

谢谢你！

Answer 1

希望这些有帮助。

1. 由于JSON数据是动态的，因此第一步是找到最大奖励序列。 （我使用了正则表达式和max_reward udf。）
2. 然后，以迭代方式从json 奖励字段中提取每个奖励。
3. 最后，使用pivot查询使结果成为宽形式。

如果需要一个更通用的解决方案，则需要使用BigQuery Dynamic SQL生成枢轴列。我已经在查询中对它们进行了硬编码。

（'ready_1'，'reward_2'，'reward_3'，'reward_4'）

查询：

CREATE TEMP TABLE sample AS 
SELECT 1 AS id, '{"rewards": { "reward_1": { ... ' AS json -- put your json here
 UNION ALL
SELECT 2 AS id, '{"rewards": { "reward_1": { ... ' AS json -- put your another json here
;

CREATE TEMP FUNCTION extract_reward(json STRING, seq INT64)
RETURNS STRUCT<type STRING, amount STRING>
LANGUAGE js AS """
  return JSON.parse(json)['reward_' + seq];
""";

CREATE TEMP FUNCTION max_reward(arr ARRAY<STRING>) AS ((
  SELECT MAX(CAST(v AS INT64)) FROM UNNEST(arr) v
));

SELECT * FROM (
  SELECT id,
         'reward_' || seq AS reward, 
         extract_reward(FORMAT('%t', JSON_QUERY(json, '$.rewards')), seq) AS value
    FROM sample, UNNEST(GENERATE_ARRAY(1, max_reward(REGEXP_EXTRACT_ALL(json, r'"reward_([0-9]+)"')))) seq
)  PIVOT (ANY_VALUE(value) FOR reward IN ('reward_1', 'reward_2', 'reward_3', 'reward_4'));

output：

”单独的列

SELECT * FROM (
  SELECT id,
         'reward_' || seq || '_' || IF (offset = 0, 'type', 'amount') AS reward,
         value
    FROM sample,
         UNNEST(GENERATE_ARRAY(1, max_reward(REGEXP_EXTRACT_ALL(json, r'"reward_([0-9]+)"')))) seq, 
         UNNEST([extract_reward(FORMAT('%t', JSON_QUERY(json, '$.rewards')), seq)]) pair,
         UNNEST([pair.type, pair.amount]) value WITH OFFSET
)  PIVOT (ANY_VALUE(value) FOR reward IN ('reward_1_type', 'reward_2_type', 'reward_3_type', 'reward_4_type', 'reward_1_amount', 'reward_2_amount', 'reward_3_amount', 'reward_4_amount'));

输出：