在单独的行上返回Redshift JSON数组的元素

Question

我有一个Redshift表，看起来像这样：

 id | metadata
---------------------------------------------------------------------------
 1  | [{"pet":"dog"},{"country":"uk"}]
 2  | [{"pet":"cat"}]
 3  | []
 4  | [{"country":"germany"},{"education":"masters"},{"country":"belgium"}]

• 所有数组元素只有一个字段。
• 不能保证特定字段将出现在数组的任何元素中。
• 字段名称可以在数组中重复
• 数组元素可以任意顺序

我想找一张看起来像这样的桌子：

 id |   field   |  value
------------------------
 1  | pet       | dog
 1  | country   | uk
 2  | pet       | cat
 4  | country   | germany
 4  | education | masters
 4  | country   | belgium

然后，我可以将其与输入表其余部分的查询结合起来。

我曾尝试过使用Redshift JSON函数，但是由于无法在Redshift中编写函数/使用循环/具有变量，我真的看不到这样做的方法！

请让我知道是否可以澄清其他问题。

Answer 1

多亏了这篇受启发的博客文章，我才得以制定解决方案。这是：

1. 创建一个查找表，以有效地“迭代”每个数组的元素。该表中的行数等于或大于数组的最大元素数。假设这是4（可以使用来计算SELECT MAX(JSON_ARRAY_LENGTH(metadata)) FROM input_table）：

        CREATE VIEW seq_0_to_3 AS
        SELECT 0 AS i UNION ALL                                      
        SELECT 1 UNION ALL
        SELECT 2 UNION ALL    
        SELECT 3          
    );

1. 由此，我们可以为每个JSON元素创建一行：

        WITH exploded_array AS (                                                                          
        SELECT id, JSON_EXTRACT_ARRAY_ELEMENT_TEXT(metadata, seq.i) AS json
        FROM input_table, seq_0_to_3 AS seq
        WHERE seq.i < JSON_ARRAY_LENGTH(metadata)
      )
    SELECT *
    FROM exploded_array;

生产：

     id | json
------------------------------
 1  | {"pet":"dog"}
 1  | {"country":"uk"}
 2  | {"pet":"cat"}
 4  | {"country":"germany"}
 4  | {"education":"masters"}
 4  | {"country":"belgium"}

1. 但是，我需要提取字段名称/值。由于我看不到使用Redshift的有限函数提取JSON字段名称的任何方法，因此我将使用正则表达式来做到这一点：

        WITH exploded_array AS (                                                                                       
        SELECT id, JSON_EXTRACT_ARRAY_ELEMENT_TEXT(metadata, seq.i) AS json
        FROM input_table, seq_0_to_3 AS seq
        WHERE seq.i < JSON_ARRAY_LENGTH(metadata)
    )
    SELECT id, field, JSON_EXTRACT_PATH_TEXT(json, field)
    FROM (
        SELECT id, json, REGEXP_SUBSTR(json, '[^{"]\\w+[^"]') AS field
        FROM exploded_array
    );