PostgreSQL: выбрать данные из другой таблицы через jsonb

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

Пример данных

1. Таблица products (товары):

   Копировать

   CREATE TABLE products (
      id SERIAL PRIMARY KEY,
      name TEXT
   );
   
   INSERT INTO products (id, name) VALUES
   (1, 'Laptop'),
   (2, 'Mouse'),
   (3, 'Keyboard');

2. Таблица orders (заказы), где JSONB-поле product_ids содержит массив ID товаров:

   Копировать

   CREATE TABLE orders (
      id SERIAL PRIMARY KEY,
      product_ids JSONB
   );
   
   INSERT INTO orders (id, product_ids) VALUES
   (1, '[1, 2]'),  -- Заказ с товарами Laptop и Mouse
   (2, '[3]');     -- Заказ с товаром Keyboard

Задача

Для каждого заказа выбрать названия товаров, соответствующих ID из массива product_ids.

Решение 1: Использование jsonb_array_elements и JOIN

Функция jsonb_array_elements разворачивает JSONB-массив в набор строк, что позволяет использовать JOIN с таблицей products.

SELECT o.id AS order_id, p.name AS product_name
FROM orders o,
     jsonb_array_elements_text(o.product_ids) AS product_id
JOIN products p ON p.id = product_id::int;

Результат:

 order_id | product_name
----------+--------------
        1 | Laptop
        1 | Mouse
        2 | Keyboard

Пояснение:

• jsonb_array_elements_text(o.product_ids) разворачивает массив product_ids в набор строк.
• product_id::int преобразует строковое значение ID в целое число для JOIN с таблицей products.

Решение 2: Использование jsonb_to_recordset

Функция jsonb_to_recordset позволяет преобразовать JSONB-массив в таблицу с определёнными колонками.

SELECT o.id AS order_id, p.name AS product_name
FROM orders o,
     jsonb_to_recordset(o.product_ids) AS product_id(id int)
JOIN products p ON p.id = product_id.id;

Результат:

 order_id | product_name
----------+--------------
        1 | Laptop
        1 | Mouse
        2 | Keyboard

Пояснение:

• jsonb_to_recordset(o.product_ids) AS product_id(id int) преобразует JSONB-массив в таблицу с одной колонкой id.
• JOIN выполняется по этой колонке.

Решение 3: Использование подзапроса и ANY

Если вам нужно получить названия товаров в виде массива для каждого заказа, можно использовать подзапрос и функцию array_agg.

SELECT o.id AS order_id,
       array_agg(p.name) AS product_names
FROM orders o,
     jsonb_array_elements_text(o.product_ids) AS product_id
JOIN products p ON p.id = product_id::int
GROUP BY o.id;

Результат:

 order_id |    product_names
----------+--------------------
        1 | {Laptop,Mouse}
        2 | {Keyboard}

Пояснение:

• jsonb_array_elements_text разворачивает массив.
• array_agg(p.name) собирает названия товаров в массив для каждого заказа.

Решение 4: Использование WHERE с jsonb_exists

Если вам нужно выбрать заказы, содержащие определённый товар, можно использовать функцию jsonb_exists.

SELECT o.id AS order_id, p.name AS product_name
FROM orders o,
     jsonb_array_elements_text(o.product_ids) AS product_id
JOIN products p ON p.id = product_id::int
WHERE jsonb_exists(o.product_ids, '2');  -- Выбрать заказы, содержащие товар с ID = 2

Результат:

 order_id | product_name
----------+--------------
        1 | Mouse

Пояснение:

• jsonb_exists(o.product_ids, '2') проверяет, содержится ли ID 2 в массиве product_ids.

Решение 5: Использование WITH (CTE) для улучшения читаемости

Если запрос сложный, можно использовать общие табличные выражения (CTE) для улучшения читаемости.

WITH order_products AS (
    SELECT o.id AS order_id, p.name AS product_name
    FROM orders o,
         jsonb_array_elements_text(o.product_ids) AS product_id
    JOIN products p ON p.id = product_id::int
)
SELECT * FROM order_products;

Результат:

 order_id | product_name
----------+--------------
        1 | Laptop
        1 | Mouse
        2 | Keyboard

Пояснение:

• WITH order_products AS (...) создаёт временную таблицу order_products, которую можно использовать в основном запросе.

Заключение

• Используйте jsonb_array_elements_text или jsonb_to_recordset для разворачивания JSONB-массива.
• Применяйте JOIN для связи с другой таблицей.
• Если нужно агрегировать данные, используйте array_agg.
• Для сложных запросов применяйте CTE (WITH).

Эти подходы помогут вам эффективно работать с JSONB-полями и выбирать данные из связанных таблиц.