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 — 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