PartiQL/SQL: запрос массива JSON-SUPER для извлечения значений в таблицу на Redshift

У меня есть несколько сложный массив SUPER, который я перенес в Redshift с помощью REST API. «API_table» в настоящее время выглядит так: пример таблицы

Один из примеров столбцов «значения» выглядит следующим образом:

ценности

[{"значение":[{"значение":"6.9","квалификаторы":["P"],"dateTime":"2023-01-30T17:30:00.000-05:00"},{"значение ":"6.9","квалификаторы":["P"],"dateTime":"2023-01-30T17:45:00.000-05:00"},{"значение":"6.9","квалификаторы": ["P"],"dateTime":"2023-01-30T18:00:00.000-05:00"},{"value":"6.9","qualifiers":["P"],"dateTime": "2023-01-30T18:15:00.000-05:00"},,{"значение":"6.8","квалификаторы":["P"],"dateTime":"2023-01-30T20:00: 00.000-05:00"},...

Я запросил данные «значения», используя:

SELECT c.values[0].value[0].value as v
FROM API_table c;

Однако это возвращает только первое значение "6,9" в каждой строке, а не все элементы "value" в строке. Тот же подход не работает для извлечения элементов «dateTime», поскольку он создает значения NULL:

SELECT c.values[0].value[0].dateTime as dt
FROM API_table c;

Приведенный выше пример напоминает только одну строку таблицы. Мой вопрос: есть ли способы запросить данные в каждой строке таблицы, чтобы все значения («значение» и «dateTime») каждой строки можно было извлечь в новую таблицу?

Желаемый результат:

в дт 6,9 2023-01-30T17:45:00.000-05:00 6,9 2023-01-30T18:00:00.000-05:00 6,9 2023-01-30T18:15:00.000-05:00

Большое спасибо.

Я попробовал следующий запрос, но он возвращал только единичные результаты «значения» для каждой строки.

SELECT c.values[0].value[0].value as v
FROM API_table c;

Применительно к элементам «dateTime» он давал значения NULL:

SELECT c.values[0].value[0].dateTime as dt
FROM API_table c;

================================================== =================

@BillWeiner спасибо, я работал как с CTE, так и с тестовыми примерами и получил желаемые результаты (особенно с CTE). Остается только одна проблема: как выбрать исходную таблицу/столбец, содержащий весь супермассив, чтобы его можно было вставить в test1 (или col1 в случае CTE).

В каждой строке столбца «значения» есть супермассивы, поэтому проблема остается при выборе столбца «значения» и извлечении каждого из множественных значений («6.9») и объектов dateTime из каждой строки.

================================================== ==============

Мне удалось выполнить запрос, когда строки json явно указаны в запросе insert into test1 values.

Теперь я запускаю этот запрос:

SET enable_case_sensitive_identifier TO true;

create table test1 (jvalues varchar(2048));

insert into test1 select c.values from ph_api c;

create table test2 as select json_parse(jvalues) as svalues from test1;

with recursive numbers(n) as
( select 0 as n
    union all
    select n + 1
    from numbers n
    where n.n < 20
), 
exp_top as
(   select c.svalues[n].value
    from test2 c
    cross join numbers n
)
,
exp_bot as
(   select c.value[n]
    from exp_top c
    cross join numbers n
    where c.value is not null
)
select *, value.value as v, value."dateTime" as dt
from exp_bot
where value is not null;

Тем не менее, я получаю сообщение об ошибке: ОШИБКА: столбец "jvalues" имеет переменный тип, но выражение имеет тип super. Подсказка: вам нужно будет переписать или преобразовать выражение. когда я пытаюсь вставить исходную таблицу с помощью insert into test1 SELECT c.values from table c;

Я хотел бы иметь возможность SELECT эти исходные данные:

исходная информация переменная ценности {"siteName":"РСЯ","siteCode":[{"значение":"01"}] {"код переменной":[{"значение":"00600","сеть":"ID"} [{"значение":[{"значение":"3.9","квалификаторы":["P"],"dateTime":"2023-01-30T17:30:00.000-05:00"},{"значение ":"4.9","квалификаторы":["P"],"dateTime":"2023-01-30T17:45:00.000-05:00"}] {"siteName":"РСЯ","siteCode":[{"значение":"01"}] {"код переменной":[{"значение":"00600","сеть":"ID"} [{"значение":[{"значение":"5.9","квалификаторы":["P"],"dateTime":"2023-01-30T18:00:00.000-05:00"},{"значение ":"6.9","квалификаторы":["P"],"dateTime":"2023-01-30T18:15:00.000-05:00"}]

как jvalues, чтобы его можно было развернуть в желаемый результат:

в дт 3,9 2023-01-30T17:30:00.000-05:00 4.9 2023-01-30T17:45:00.000-05:00 5,9 2023-01-30T18:00:00.000-05:00 6,9 2023-01-30T18:15:00.000-05:00

================================================== ==============

Следующий запрос работал для выбора нужных строк json:

with exp_top as
(   select s.value 
    from <source_table> c, c.values s
)
select s.value, s."dateTime" from exp_top c, c.value s;
ReactJs | Supabase | Добавление данных в базу данных
ReactJs | Supabase | Добавление данных в базу данных
Это и есть ваш редактор таблиц в supabase.👇
Понимание Python и переход к SQL
Понимание Python и переход к SQL
Перед нами лабораторная работа по BloodOath:
0
0
68
1
Перейти к ответу Данный вопрос помечен как решенный

Ответы 1

Ответ принят как подходящий

Да. Вам нужно расширить каждый элемент массива в свою собственную строку. Для расширения массивов в строки потребуется рекурсивный CTE (или что-то подобное). Это можно сделать на основе максимальной длины массива в супер или с некоторым фиксированным набором чисел. Этот набор чисел нужно будет скрестить с вашей таблицей, чтобы извлечь каждый элемент массива.

Ранее я писал аналогичный ответ — Извлечь значение на основе определенного ключа из массива jsons в Amazon Redshift — посмотрите и посмотрите, поможет ли это вам. Дайте мне знать, если вам нужна помощь в адаптации этого к вашей ситуации.

================================================== ============

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

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

Сначала настройте тестовые данные:

SET enable_case_sensitive_identifier TO true;

create table test1 (jvalues varchar(2048));

insert into test1 values
('[{"value":[{"value":"6.9","qualifiers":["P"],"dateTime":"2023-01-30T17:30:00.000-05:00"},{"value":"6.9","qualifiers":["P"],"dateTime":"2023-01-30T17:45:00.000-05:00"},{"value":"6.9","qualifiers":["P"],"dateTime":"2023-01-30T18:00:00.000-05:00"},{"value":"6.9","qualifiers":["P"],"dateTime":"2023-01-30T18:15:00.000-05:00"},{"value":"6.9","qualifiers":["P"],"dateTime":"2023-01-30T18:30:00.000-05:00"},{"value":"6.9","qualifiers":["P"],"dateTime":"2023-01-30T18:45:00.000-05:00"},{"value":"6.9","qualifiers":["P"],"dateTime":"2023-01-30T19:00:00.000-05:00"},{"value":"6.9","qualifiers":["P"],"dateTime":"2023-01-30T19:15:00.000-05:00"},{"value":"6.9","qualifiers":["P"],"dateTime":"2023-01-30T19:30:00.000-05:00"},{"value":"6.9","qualifiers":["P"],"dateTime":"2023-01-30T19:45:00.000-05:00"},{"value":"6.8","qualifiers":["P"],"dateTime":"2023-01-30T20:00:00.000-05:00"}]}, {"value":[{"value":"6.9","qualifiers":["P"],"dateTime":"2023-01-30T17:30:00.000-05:00"},{"value":"6.9","qualifiers":["P"],"dateTime":"2023-01-30T17:45:00.000-05:00"},{"value":"6.9","qualifiers":["P"],"dateTime":"2023-01-30T18:00:00.000-05:00"},{"value":"6.9","qualifiers":["P"],"dateTime":"2023-01-30T18:15:00.000-05:00"},{"value":"6.9","qualifiers":["P"],"dateTime":"2023-01-30T18:30:00.000-05:00"},{"value":"6.9","qualifiers":["P"],"dateTime":"2023-01-30T18:45:00.000-05:00"},{"value":"6.9","qualifiers":["P"],"dateTime":"2023-01-30T19:00:00.000-05:00"},{"value":"6.9","qualifiers":["P"],"dateTime":"2023-01-30T19:15:00.000-05:00"},{"value":"6.9","qualifiers":["P"],"dateTime":"2023-01-30T19:30:00.000-05:00"},{"value":"6.9","qualifiers":["P"],"dateTime":"2023-01-30T19:45:00.000-05:00"},{"value":"6.8","qualifiers":["P"],"dateTime":"2023-01-30T20:00:00.000-05:00"}]}]'),
('[{"value":[{"value":"5.9","qualifiers":["P"],"dateTime":"2023-01-30T17:30:00.000-05:00"},{"value":"5.9","qualifiers":["P"],"dateTime":"2023-01-30T17:45:00.000-05:00"},{"value":"8.9","qualifiers":["P"],"dateTime":"2023-01-30T18:00:00.000-05:00"},{"value":"6.9","qualifiers":["P"],"dateTime":"2023-01-30T18:15:00.000-05:00"},{"value":"6.9","qualifiers":["P"],"dateTime":"2023-01-30T18:30:00.000-05:00"},{"value":"6.9","qualifiers":["P"],"dateTime":"2023-01-30T18:45:00.000-05:00"},{"value":"6.9","qualifiers":["P"],"dateTime":"2023-01-30T19:00:00.000-05:00"},{"value":"6.9","qualifiers":["P"],"dateTime":"2023-01-30T19:15:00.000-05:00"},{"value":"6.9","qualifiers":["P"],"dateTime":"2023-01-30T19:30:00.000-05:00"},{"value":"6.9","qualifiers":["P"],"dateTime":"2023-01-30T19:45:00.000-05:00"},{"value":"6.8","qualifiers":["P"],"dateTime":"2023-01-30T20:00:00.000-05:00"}]}, {"value":[{"value":"6.9","qualifiers":["P"],"dateTime":"2023-01-30T17:30:00.000-05:00"},{"value":"6.9","qualifiers":["P"],"dateTime":"2023-01-30T17:45:00.000-05:00"},{"value":"6.9","qualifiers":["P"],"dateTime":"2023-01-30T18:00:00.000-05:00"},{"value":"6.9","qualifiers":["P"],"dateTime":"2023-01-30T18:15:00.000-05:00"},{"value":"6.9","qualifiers":["P"],"dateTime":"2023-01-30T18:30:00.000-05:00"},{"value":"6.9","qualifiers":["P"],"dateTime":"2023-01-30T18:45:00.000-05:00"},{"value":"6.9","qualifiers":["P"],"dateTime":"2023-01-30T19:00:00.000-05:00"},{"value":"6.9","qualifiers":["P"],"dateTime":"2023-01-30T19:15:00.000-05:00"},{"value":"6.9","qualifiers":["P"],"dateTime":"2023-01-30T19:30:00.000-05:00"},{"value":"6.9","qualifiers":["P"],"dateTime":"2023-01-30T19:45:00.000-05:00"},{"value":"6.8","qualifiers":["P"],"dateTime":"2023-01-30T20:00:00.000-05:00"}]}]');

create table test2 as select json_parse(jvalues) as svalues from test1;

Обратите внимание, что мы должны включить чувствительность к регистру, чтобы сеанс мог правильно выбрать «dateTime».

Затем разверните массивы вручную:

with recursive numbers(n) as
( select 0 as n
    union all
    select n + 1
    from numbers n
    where n.n < 20
), 
exp_top as
(   select row_number() over () as r, n as x, c.svalues[n].value
    from test2 c
    cross join numbers n
)
,
exp_bot as
(   select r, x, n as y, c.value[n]
    from exp_top c
    cross join numbers n
    where c.value is not null
)
select *, value.value as v, value."dateTime" as dt
from exp_bot
where value is not null;

Эта версия

  1. создает числа 0 - 19,
  2. расширяет внешний массив (2 элемента в каждой строке) путем перекрестного соединения с этими номерами,
  3. расширяет внутренний массив тем же методом,
  4. дает желаемые результаты

Redshift имеет встроенный метод для развертывания супермассивов, и он определен в предложении FROM. Вы можете получить те же результаты из:

with exp_top as (select inx1, s.value from test2 c, c.svalues s at inx1)
select inx1, inx2, c.value[inx2] as value, s.value, s."dateTime" from exp_top c, c.value s at inx2;

Гораздо компактнее. Этот код был протестирован и работает как есть в Redshift. Если вы видите значение «dateTime» как NULL, скорее всего, у вас не включена чувствительность к регистру.

================================================== ========

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

with exp_top as (select c.svalues, inx1, s.value from test2 c, c.svalues s at inx1)
select svalues, inx1, inx2, c.value[inx2] as value, s.value, s."dateTime" from exp_top c, c.value s at inx2;

================================================== ========

Я думаю, что развернуть ваши фактические данные будет проще, чем код, который я предоставил для общего вопроса.

Во-первых, вам не нужно использовать таблицы test1 и test2, вы можете напрямую запрашивать свою таблицу. Если вы все еще хотите использовать test2, используйте свою таблицу в качестве источника оператора "create table test2...". Но давайте посмотрим, сможем ли мы просто использовать вашу исходную таблицу.

with exp_top as (
  select s.value from <your table> c, c.values s
)
select s.value, s."dateTime" from exp_top c, c.value s;

Этот код не проверен, но должен работать.

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

nbk 15.02.2023 00:34

Возможно, возможно нет. Кто-то, погруженный в искусство, сможет «отклеиться», посмотрев на предыдущий ответ, который «рифмуется». Кому-то, кто новичок в этих инструментах, вероятно, потребуется больше указаний. Я добавлю еще описание необходимых действий для решения вашей проблемы.

Bill Weiner 15.02.2023 01:12

Спасибо за полезную информацию. Мне удалось заставить его работать, когда строка JSON полностью указана в: inputtext as ( select '[{"value":"6.9","qualifiers":["P"],"dateTime":"2023-01-30T1‌ ​7:30:00.000-05:00"},‌​]'::text as col1 ) Но не тогда, когда я пытаюсь указать таблицу, чтобы получить полные результаты как: inputtext as ( select c.values[0].value [0].value::text as col1 из таблицы c) Любое направление здесь будет высоко оценено.

camaya 15.02.2023 01:27

@camaya ответь правильно и прими это

nbk 15.02.2023 05:02

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

Bill Weiner 15.02.2023 19:35

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

camaya 16.02.2023 17:14

Это прямо вперед - вам просто нужно передать значения через CTE (ы). Я добавлю к ответу и обновлю компактный код, чтобы показать это.

Bill Weiner 16.02.2023 18:17

@BillWeiner Спасибо за обновление. Я имел в виду, как вставить исходную таблицу в запрос «вставить в значения test1» вместо отдельных строк json, чтобы можно было развернуть таблицу в целом, а не перемещать строки json в ' вставить в запрос значений test1 отдельно.

camaya 16.02.2023 21:45

Итак, если я вас правильно понял, вы хотите напрямую вставить значения в суперстолбец без использования таблицы test1, верно? Один шаг против двух? И это нужно делать через значения данных SQL, а не через чтение данных из S3, верно? Будет ли перемещение первого выбора в CTE соответствовать требованию? Или я все еще пропускаю вопрос?

Bill Weiner 16.02.2023 21:51

@BillWeiner Идея состоит в том, чтобы вставить значения из исходного столбца супертаблицы, который представляет собой таблицу данных SQL, а не S3, в таблицу test1 почти так же, как вы сделали в своем ответе со строками json. Я думаю, что перемещение первого выбора в CTE будет соответствовать требованию, если этот выбор вызывает исходный столбец супертаблицы в это CTE.

camaya 16.02.2023 22:07

Стараясь не думать об этом. Вы ищете «INSERT в test2 SELECT <super column> from <table2>:». Это то, что вы ищите?

Bill Weiner 16.02.2023 22:59

@BillWeiner Я ищу что-то, чтобы выбрать исходную таблицу, в соответствии со строками «вставить в test1 SELECT <super column> FROM <original_table>;» чтобы я мог использовать значения json в исходной таблице и развернуть их с остальной частью предоставленного вами сценария.

camaya 16.02.2023 23:19

Итак, INSERT INTO test SELECT A, B, C, <super> from <table>; ??? Затем просто несите их через запрос. Если я все еще не понимаю, что вы делаете после того, как вы захотите обновить вопрос, чтобы показать исходные данные и желаемый результат.

Bill Weiner 16.02.2023 23:47

@BillWeiner Хорошо, я обновил вопрос, потому что не смог заставить работать SELECT <исходную таблицу>.

camaya 17.02.2023 01:13

@BillWeiner, у меня наконец-то получилось. Большое спасибо за вашу помощь!

camaya 17.02.2023 16:52

Другие вопросы по теме