PostgreSQL : JSON 배열의 JSON 값을 기준으로 행 정렬

Question

테이블 에는 JSON 개체의 배열을 저장하는 productsJSONB 열이 있다고 말합니다 identifiers.

제품의 샘플 데이터

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

이제 "amzn.com"도메인의 "id"값을 기준으로 테이블의 요소를 정렬하는 쿼리를 작성해야합니다.

예상 결과

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

의 ID amzn.com는 "amzn-123"및 "amzn-234"입니다. amzn.com의 ID로 정렬하면 "amzn-123"이 먼저 나타난 다음 "amzn-234"가 나타납니다.

도메인 "amzn.com"에 대한 "id"값으로 테이블을 정렬하면 amzn.com의 ID가 NULL이기 때문에 ID가 3 인 레코드가 먼저 나타나고 그 뒤에 유효한 ID가있는 ID 1과 2가있는 레코드가 표시됩니다. 정렬됩니다.

이 사용 사례에 대한 쿼리를 작성하는 방법에 대해 진정으로 단서가 없습니다. JSONB이고 JSON 배열이 아니라면 시도했을 것입니다.

PostgreSQL에서 이러한 사용 사례에 대한 쿼리를 작성할 수 있습니까? 그렇다면 적어도 의사 코드 또는 대략적인 쿼리를 제공하십시오.

Answer 1

배열의 위치를 ​​모르기 때문에 모든 배열 요소를 반복하여 Amazon ID를 찾아야합니다.

ID가 있으면 order by. 를 사용 nulls first하면 아마존 ID가없는 맨 위에 해당 제품이 배치됩니다.

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