Извлечь все значения имени из массива json в Redshift

У меня есть поле json со значениями:

[{"elementId": "1", "name": "foo", "value": "A"}, {"elementId": "2", "name": "bar", "value": "B"}, {"elementId": "3", "name": "foobar", "value": "C"}, {"elementId": "4", "name": "barfoo", "value": "D"}]

Итак, мой набор данных будет выглядеть так:

user_id | form_data
---------------------------------------------
101 | [{"elementId": "1", "name": "foo", "value": "A"}, {"elementId": "2", "name": "bar", "value": "B"}, {"elementId": "3", "name": "foobar", "value": "C"}, {"elementId": "4", "name": "barfoo", "value": "D"}]
102 | [{"elementId": "1", "name": "crash", "value": "A"}, {"elementId": "2", "name": "bang", "value": "B"}, {"elementId": "3", "name": "wallop", "value": "C"}]

Я хотел бы извлечь значения имени из списка, чтобы в качестве вывода у меня был столбец списка, разделенный запятыми:

user_id | names
----------------------------
101 | foo,bar,foobar,barfoo
102 | crash,bang,wallop

Список может быть разной длины, поэтому мой текущий подход (ниже) не будет работать с более длинными формами:

SELECT a || ',' || b || ',' || c || ',' || d
FROM (select f.form_data
      ,JSON_EXTRACT_PATH_TEXT(json_extract_array_element_text (f.form_data,0),'name') a
      ,JSON_EXTRACT_PATH_TEXT(json_extract_array_element_text (f.form_data,1),'name') b
      ,JSON_EXTRACT_PATH_TEXT(json_extract_array_element_text (f.form_data,2),'name') c
      ,JSON_EXTRACT_PATH_TEXT(json_extract_array_element_text (f.form_data,3),'name') d
      FROM forms f)

Любая помощь будет принята с благодарностью!

Я могу извлечь все значения имени в виде отдельных столбцов, используя приведенное выше, но проблема здесь в том, что я должен ссылаться на каждое индексированное имя позиции, в котором может появиться, и это не масштабируется.

Чего я пытаюсь добиться, так это извлечь все компоненты имени в одну ячейку, не ссылаясь на позицию в массиве.


arrays json arraylist amazon-redshift
person Charlie B    schedule 21.04.2021    source источник

Ответы (2)


arrow_upward
0
arrow_downward

Основываясь на этом обновлении, я думаю, вы просто хотите расширить массив json, а затем объединить имена в список. Соответствует ли это вашим потребностям?

with num1024 as (
SELECT 
    (p0.n + p1.n*2 + p2.n * POWER(2,2) + p3.n * POWER(2,3) + p4.n * POWER(2,4) + p5.n * POWER(2,5) 
        + p6.n * POWER(2,6) + p7.n * POWER(2,7) + p8.n * POWER(2,8) + p9.n * POWER(2,9))::int as n
  FROM 
    (SELECT 0 as n UNION SELECT 1) p0,
    (SELECT 0 as n UNION SELECT 1) p1,
    (SELECT 0 as n UNION SELECT 1) p2,
    (SELECT 0 as n UNION SELECT 1) p3,
    (SELECT 0 as n UNION SELECT 1) p4,
    (SELECT 0 as n UNION SELECT 1) p5,
    (SELECT 0 as n UNION SELECT 1) p6,
    (SELECT 0 as n UNION SELECT 1) p7,
    (SELECT 0 as n UNION SELECT 1) p8,
    (SELECT 0 as n UNION SELECT 1) p9
  Order by 1
)
select id, listagg(a, ',') within group (order by depth) as names
from (
        select f.id
              ,JSON_EXTRACT_PATH_TEXT(json_extract_array_element_text (f.form_data,n.n),'name') a
              ,n.n as depth
        FROM forms f, num1024 n
        where a <> '' )
group by id
order by id
;

Работает до 1024 элементов массива.

person Bill Weiner    schedule 21.04.2021
comment
Да, это именно то, что я ищу, спасибо! Я не совсем уверен, что понимаю, что вы делаете в CTE? - person Charlie B; 22.04.2021
comment
CTE просто создает виртуальную таблицу чисел от 0 до 1023. Есть много способов сделать это, но этот не делает предположений о том, что еще у вас есть в вашем кластере. Поскольку в нем всего 1024 числа, это самый длинный массив, который этот запрос может полностью извлечь. - person Bill Weiner; 22.04.2021

arrow_upward
0
arrow_downward

Похоже, это вариант использования для RECURSIVE CTE. Взгляните на этот пример, он кажется почти тем, что вам нужно — https://docs.aws.amazon.com/redshift/latest/dg/r_WITH_clause.html

person Bill Weiner    schedule 21.04.2021
comment
Я не уверен на 100%, что это то, что я ищу - я думаю, что основная проблема, с которой я столкнулся, - это возможность извлечь данные из массива/списка json в формате, похожем на идентификатор, - без необходимости проверять каждая позиция индекса в отдельности. Я отредактировал основной пост, чтобы попытаться отразить это - person Charlie B; 21.04.2021