Categories: PostgreSQL

PostgreSQL | Как преобразовать JSONB-массив строк в SQL-массив строк?

Казалось бы такое простое действие, но когда это касается PostgreSQL то сразу всё становится сложно. Тупизна заключается в том, что в отличии от классических массивов в JavaScript, массивы PostgreSQL могут иметь понятие «размерности».

Проблема преобразований может выстрелить, когда вы будете писать свои собственные функции, которые на входе будут принимать JSONB и возвращать также JSONB. Будет что-то вроде: За «[» должны следовать явно задаваемые размерности массива.»

Также можно получить ошибки вида «cannot cast type jsonb to integer[]» или «cannot cast type jsonb to text[]» если пытаться сделать такое приведение:

select ('[333,555]'::jsonb)::int[];
select ('["333","555"]'::jsonb)::text[];

Сразу к решению.

Решение № 1

Использование функции «translate()«, которая умеет работать со строками:

select translate(('["cat","dog"]'::jsonb)::text, '[]', '{}')::text[] as words_array;

Сначала мы приводим наш JSONB-массив к обычному тексту. Если смотреть на это приведение со стороны JavaScript, то это действие отдаёт бредом потому что в языке JavaScript такая сущность как JSON уже является строкой само по себе. Но в PostgreSQL всё иначе.

Вторым параметром для «translate()» мы передаём символы квадратных скобок. Из мы хотим заменить на фигурные скобки. В этом месте мы получим строку вида:

{"cat", "dog"}

Эту строку мы уже можем преобразовать в PostgreSQL-массив из элементов с типом text. Для этого ставим два двоеточия, пишем слово «text» и дописываем две квадратные скобки.

После этого произойдёт магия и двойные кавычки будут выброшены после трансформаций.

На выходе мы получим интересующий нас SQL-массив из строк.

JSONB-array text to SQL-array text — PostgreSQL

В данном примере мы получим SQL-массив из слов «cat» и «dog» («кошка» и «собака»).

 

Решение № 2

Если у нас значение какого-либо ключа объекта является массивом из строк (только строк!), то такое преобразование можно выполнить ещё одним способом:

select 
    array_agg(word) as words 
from (
    select jsonb_array_elements_text('{"words":["container", "dental"]}'::jsonb -> 'words')
) as Q(word);

Сначала мы извлекаем нужное нам значение (JSONB-массив) по ключу «words». Делаем это при помощи оператора «дефис и правая угловая скобка» чтобы вытащить JSONB-значение.

Основу для нашего решения составляет функция «jsonb_array_elements_text()«. Она разворачивает JSONB-массив верхнего уровня в набор значений «text«. То есть мы получим один столбик для таблицы, где записями будут являться наши слова с типом «text«. Если на верхнем уровне будут встречаться массивы, то по итогу мы всё равно получим строки, где в некоторых из них будут квадратные скобки. Это всё мы обернём в отдельный запрос.

После всех манипуляций нам останется применить функцию агрегации значений по этому столбику. Так как нам нужен SQL-массив из строк, то мы будем использовать агрегатную функцию «array_agg()«.

На выходе данное выражение вернёт нам заветный SQL-массив вместо JSONB-массива.

 

Информационные ссылки

Функция translate()

Функция jsonb_array_elements_text()

Официальный сайт WEB-оболочки pgAdmin — https://www.pgadmin.org

Официальный сайт СУБД PostgreSQL — https://www.postgresql.org

admin

Share
Published by
admin

Recent Posts

PostgreSQL | Как получить первый элемент из ячейки типа record?

При помощи функции "row()" мы можем создавать записи - данные с типом record. -- Создаём…

1 год ago

PostgreSQL | Как отфильтровать JSONB-массив объектов по логическому значению ключа?

-- массив JSONB с объектами select '[{"id":1, "q":true}, {"id":2, "q":false}, {"id":3, "q":true}, {"id":4, "q":false}]'::jsonb; --…

1 год ago

PostgreSQL | Как отбросить нули в типе numeric?

Функция "trim_scale()" умеет отбрасывать лишние нули в числах с типом numeric. select trim_scale(37.000000000); -- 37…

1 год ago

JavaScript | Как получить следующий день?

Следующий день относительно текущего т. е. "завтра" var tomorrow = new Date(); tomorrow.setDate(tomorrow.getDate() + 1);…

1 год ago

PostgreSQL | Агрегация JSONB-объектов в один объект

У нас есть столбец из JSONB объектов. Нам нужно соединить все объекты этого столбца в…

1 год ago

PostgreSQL | Группировка по 10 записей

Не всегда нужно группировать записи таблиц в рамках какого-то повторяющегося значения столбца. Иногда нужно просто…

1 год ago