Как я могу преобразовать список словарей JSON в список строк в Snowflake?

У меня есть таблица Snowflake, которая содержит столбец id и столбец favorite_books. Столбец favorite_books содержит список словарей в формате JSON. Каждый словарь имеет атрибут title и ISBN:

ID | favorite_books
--------------------
42 | [{"title": "LotR", "ISBN": "9780261102354"}, {"title": "HP1", "ISBN": "9780545582889"}]
13 | [{"title": "Faust", "ISBN": "9780192835956"}]

Мой реальный случай более сложный, но здесь не хватает только заголовков. Итак, результат должен быть:

ID | favorite_books
--------------------
42 | ["LotR", "HP1"]
13 | ["Faust"]

Я видел запрос полуструктурированных данных, но я до сих пор не знаю, как это сделать.


json sql snowflake-cloud-data-platform
person Martin Thoma    schedule 25.06.2020    source источник
comment
Отвечает ли это на ваш вопрос? Как применить функцию к каждому элементу столбца массива?   -  person    schedule 25.06.2020

Ответы (1)


arrow_upward
1
arrow_downward

Адаптация существующего ответа для аналогичная структура данных, вы можете попробовать это следующим образом (используя CTE, взорвав массив, а затем снова сгруппировав его в новый массив на основе идентификатора):

with elements as (
    select ID, elements.value:title as title
    from table_name, LATERAL FLATTEN(input => favorite_books) elements
)
select elements.ID, ARRAY_AGG(elements.title) as favorite_books from elements
group by ID;

Или через UDF javascript, который более прост (просто преобразует массив):

create or replace function extract_titles(A array)
  returns array
  language javascript
  strict
  as
  $$
    return A.map(function(d) {return d.title});
  $$
;

select ID, EXTRACT_TITLES(favorite_books) as favorite_books from table_name;
person Community    schedule 25.06.2020