Блог

В таблице указано, что products есть столбец JSONB с именем identifiers , в котором хранится массив объектов JSON.

Примеры данных в продуктах

  id  |     name    |      identifiers
-----|-------------|---------------------------------------------------------------------------------------------------------------
  1  | umbrella    | [{"id": "productID-umbrella-123", "domain": "ecommerce.com"}, {"id": "amzn-123", "domain": "amzn.com"}]
  2  | ball        | [{"id": "amzn-234", "domain": "amzn.com"}]
  3  | bat         | [{"id": "productID-bat-234", "domain": "ecommerce.com"}]
  

Теперь мне нужно написать запрос, который сортирует элементы в таблице на основе значения «id» для домена «amzn.com «

Ожидаемый результат

  id   |     name     |      identifiers
----- |--------------|---------------------------------------------------------------------------------------------------------------
  3   | bat          |  [{"id": "productID-bat-234", "domain": "ecommerce.com"}]
  1   | umbrella     |  [{"id": "productID-umbrella-123", "domain": "ecommerce.com"}, {"id": "amzn-123", "domain": "amzn.com"}]
  2   | ball         |  [{"id": "amzn-234", "domain": "amzn.com"}]
  

идентификаторами amzn.com являются «amzn-123» и «amzn-234».
При сортировке по идентификаторам amzn.com сначала появляется «amzn-123», за которым следует «amzn-234»

Упорядочивание таблицы по значениям «id» для домена «amzn.com «запись с идентификатором 3 появляется первой, поскольку идентификатор для amzn.com имеет значение NULL, за которым следует запись с идентификаторами 1 и 2, которая имеет действительный идентификатор, который сортируется.

Я действительно не знаю, как я мог бы написать запрос для этого варианта использования. Если бы это был JSONB, а не массив JSON, я бы попробовал.

Возможно ли написать запрос для такого варианта использования в PostgreSQL? Если да, пожалуйста, хотя бы дайте мне псевдокод или приблизительный запрос.

Поскольку вы не знаете позицию в массиве, вам нужно будет перебрать все элементы массива, чтобы найти идентификатор Amazon.

Как только у вас есть идентификатор, вы можете использовать его с order by . Использование nulls first помещает наверх те продукты, у которых нет идентификатора Amazon.

 select p.*, a.amazon_id
from products p
   left join lateral (
      select item ->> 'id' as amazon_id
      from jsonb_array_elements(p.identifiers) as x(item)
      where x.item ->> 'domain' = 'amzn.com'
      limit 1 --<< safe guard in case there is more than one amazon id
   ) a on true --<< we don't really need a join condition
order by a.amazon_id nulls first;
  

Онлайн-пример

С Postgres 12 это было бы немного короче:

 select p.*
from products p
order by jsonb_path_query_first(identifiers, '$[*] ? (@.domain == "amzn.com").id') nulls first
  

После нескольких настроек этот запрос, наконец, был выполнен,

 select p.*, amzn -> 'id' AS amzn_id
from products p left join lateral JSONB_ARRAY_ELEMENTS(p.identifiers) amzn ON amzn->>'domain' = 'amzn.com' 
order by amzn_id nulls first