У меня есть несколько сложный массив 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») каждой строки можно было извлечь в новую таблицу?
Желаемый результат:
Большое спасибо.
Я попробовал следующий запрос, но он возвращал только единичные результаты «значения» для каждой строки.
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
эти исходные данные:
как jvalues, чтобы его можно было развернуть в желаемый результат:
================================================== ==============
Следующий запрос работал для выбора нужных строк 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;
Да. Вам нужно расширить каждый элемент массива в свою собственную строку. Для расширения массивов в строки потребуется рекурсивный 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;
Эта версия
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;
Этот код не проверен, но должен работать.
Возможно, возможно нет. Кто-то, погруженный в искусство, сможет «отклеиться», посмотрев на предыдущий ответ, который «рифмуется». Кому-то, кто новичок в этих инструментах, вероятно, потребуется больше указаний. Я добавлю еще описание необходимых действий для решения вашей проблемы.
Спасибо за полезную информацию. Мне удалось заставить его работать, когда строка 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 ответь правильно и прими это
Я добавил в свой ответ рабочий тестовый пример, который, надеюсь, вам поможет. Ваш комментарий оставляет слишком много информации, чтобы ответить прямо. Если вы работаете с добавленным примером, вы должны увидеть, как получить желаемые результаты. Если нет, предоставьте полный тестовый пример ваших данных, текущий прогресс и описание проблемы на исходный вопрос.
@BillWeiner Я обновил вопрос, чтобы отразить только то, чего не хватает для решения проблемы.
Это прямо вперед - вам просто нужно передать значения через CTE (ы). Я добавлю к ответу и обновлю компактный код, чтобы показать это.
@BillWeiner Спасибо за обновление. Я имел в виду, как вставить исходную таблицу в запрос «вставить в значения test1» вместо отдельных строк json, чтобы можно было развернуть таблицу в целом, а не перемещать строки json в ' вставить в запрос значений test1 отдельно.
Итак, если я вас правильно понял, вы хотите напрямую вставить значения в суперстолбец без использования таблицы test1, верно? Один шаг против двух? И это нужно делать через значения данных SQL, а не через чтение данных из S3, верно? Будет ли перемещение первого выбора в CTE соответствовать требованию? Или я все еще пропускаю вопрос?
@BillWeiner Идея состоит в том, чтобы вставить значения из исходного столбца супертаблицы, который представляет собой таблицу данных SQL, а не S3, в таблицу test1 почти так же, как вы сделали в своем ответе со строками json. Я думаю, что перемещение первого выбора в CTE будет соответствовать требованию, если этот выбор вызывает исходный столбец супертаблицы в это CTE.
Стараясь не думать об этом. Вы ищете «INSERT в test2 SELECT <super column> from <table2>:». Это то, что вы ищите?
@BillWeiner Я ищу что-то, чтобы выбрать исходную таблицу, в соответствии со строками «вставить в test1 SELECT <super column> FROM <original_table>;» чтобы я мог использовать значения json в исходной таблице и развернуть их с остальной частью предоставленного вами сценария.
Итак, INSERT INTO test SELECT A, B, C, <super> from <table>; ??? Затем просто несите их через запрос. Если я все еще не понимаю, что вы делаете после того, как вы захотите обновить вопрос, чтобы показать исходные данные и желаемый результат.
@BillWeiner Хорошо, я обновил вопрос, потому что не смог заставить работать SELECT <исходную таблицу>.
@BillWeiner, у меня наконец-то получилось. Большое спасибо за вашу помощь!
разве вы не должны голосовать за это как за дубликат, также это не более чем ответ только по ссылке