Возвращать элементы массива Redshift JSON в отдельных строках

У меня есть таблица 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, я действительно не вижу способа сделать это!

Пожалуйста, дайте мне знать, если я могу прояснить что-нибудь еще.


json amazon-redshift
person Serenthia    schedule 12.05.2015    source источник

Ответы (2)


arrow_upward
12
arrow_downward

Благодаря этому вдохновленному сообщению в блоге я смог создать решение. Это:

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

  2. Исходя из этого, мы можем создать одну строку для каждого элемента 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"}

  3. Однако мне нужно было извлечь имена/значения полей. Поскольку я не вижу способа извлечь имена полей JSON с помощью ограниченных функций Redshift, я сделаю это с помощью регулярного выражения:

    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
);
person Serenthia    schedule 12.05.2015

arrow_upward
2
arrow_downward

Существует общая версия для CREATE VIEW seq_0_to_3. Назовем его CREATE VIEW seq_0_to_n. Это может быть сгенерировано

CREATE VIEW seq_0_to_n AS (  
    SELECT row_number() over (
                          ORDER BY TRUE)::integer - 1 AS i
    FROM <insert_large_enough_table> LIMIT <number_less_than_table_entries>);

Это помогает создавать большие последовательности в виде представления.

person Dheeraj M R    schedule 02.12.2016