PostgreSQL: classificar as linhas com base no valor de um JSON em uma matriz de JSON

Question

Uma tabela diz productsque existe uma coluna JSONB chamada identifiersque armazena uma matriz de objetos JSON.

Dados de amostra em produtos

 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"}]

Agora, tenho que escrever uma consulta que classifique os elementos da tabela com base no valor "id" para o domínio "amzn.com"

Resultado esperado

 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"}]

ids de amzn.comsão "amzn-123" e "amzn-234". Quando classificado por ids de amzn.com, "amzn-123" aparece primeiro, seguido por "amzn-234"

Ordenando a tabela pelos valores de "id" para o domínio "amzn.com", o registro com id 3 aparece primeiro, pois o id para amzn.com é NULL, seguido por um registro com id 1 e 2, que tem um id válido está classificado.

Não tenho ideia de como escrever uma consulta para esse caso de uso. Se fosse um JSONB e não um array de JSON, eu teria tentado.

É possível escrever uma consulta para esse caso de uso no PostgreSQL? Se sim, por favor, pelo menos me dê um pseudocódigo ou uma consulta aproximada.

Answer 1

Como você não sabe a posição no array, precisará iterar todos os elementos do array para encontrar o ID do amazon.

Depois de obter o ID, você pode usá-lo com um order by. Usar nulls firstcoloca os produtos no topo que não têm um ID 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;

Exemplo online

---

Com o Postgres 12, isso seria um pouco mais curto:

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

Answer 2

Depois de alguns ajustes, esta é a consulta que finalmente chegou,

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