PostgreSQL: Mengurutkan baris berdasarkan nilai JSON dalam array JSON

Question

Sebuah tabel mengatakan productsmemiliki kolom JSONB yang disebut identifiersyang menyimpan larik objek JSON.

Contoh data dalam produk

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

Sekarang, saya harus menulis kueri yang mengurutkan elemen dalam tabel berdasarkan nilai "id" untuk domain "amzn.com"

Hasil yang diharapkan

 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 dari amzn.comare "amzn-123" dan "amzn-234". Saat diurutkan berdasarkan id dari amzn.com, "amzn-123" muncul pertama kali, diikuti oleh "amzn-234"

Mengurutkan tabel dengan nilai "id" untuk domain "amzn.com", record dengan id 3 muncul pertama kali karena id untuk amzn.com adalah NULL, diikuti oleh record dengan id 1 dan 2, yang memiliki id valid yang diurutkan.

Saya benar-benar tidak mengerti bagaimana saya bisa menulis kueri untuk kasus penggunaan ini. Jika itu JSONB dan bukan array JSON saya akan mencoba.

Apakah mungkin untuk menulis kueri untuk kasus penggunaan seperti itu di PostgreSQL? Jika ya, setidaknya beri saya kode palsu atau pertanyaan kasar.

Answer 1

Karena Anda tidak mengetahui posisi dalam larik, Anda perlu mengulang semua elemen larik untuk menemukan ID amazon.

Setelah Anda memiliki ID, Anda dapat menggunakannya dengan order by. Menggunakan nulls firstmenempatkan produk tersebut di bagian atas yang tidak memiliki 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;

Contoh online

---

Dengan Postgres 12 ini akan menjadi sedikit lebih pendek:

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

Answer 2

Setelah beberapa penyesuaian, inilah kueri yang akhirnya berhasil,

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