PostgreSQL: сортировка строк на основе значения JSON в массиве JSON

Question

В таблице указано, 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? Если да, пожалуйста, дайте мне хотя бы псевдокод или примерный запрос.

Answer 1

Поскольку вы не знаете позицию в массиве, вам нужно будет перебрать все элементы массива, чтобы найти идентификатор 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

Answer 2

После нескольких настроек это запрос, который наконец-то сделал это,

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