PostgreSQL: Sortieren der Zeilen basierend auf dem Wert eines JSON in einem Array von JSON

Question

In einer Tabelle heißt es, productsdass eine JSONB-Spalte aufgerufen wird identifiers, in der ein Array von JSON-Objekten gespeichert ist .

Beispieldaten in Produkten

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

Jetzt muss ich eine Abfrage schreiben, die die Elemente in der Tabelle basierend auf dem Wert "id" für die Domain "amzn.com" sortiert.

Erwartetes Ergebnis

 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 von amzn.comsind "amzn-123" und "amzn-234". Bei der Sortierung nach IDs von amzn.com wird zuerst "amzn-123" angezeigt, gefolgt von "amzn-234".

Wenn Sie die Tabelle nach den Werten "id" für die Domain "amzn.com" sortieren, wird zuerst der Datensatz mit der ID 3 angezeigt, da die ID für amzn.com NULL ist, gefolgt von einem Datensatz mit den IDs 1 und 2, der eine gültige ID hat ist sortiert.

Ich habe wirklich keine Ahnung, wie ich eine Abfrage für diesen Anwendungsfall schreiben könnte. Wenn es ein JSONB und kein Array von JSON wäre, hätte ich es versucht.

Ist es möglich, eine Abfrage für einen solchen Anwendungsfall in PostgreSQL zu schreiben? Wenn ja, geben Sie mir bitte mindestens einen Pseudocode oder die grobe Abfrage.

Answer 1

Da Sie die Position im Array nicht kennen, müssen Sie alle Array-Elemente durchlaufen, um die Amazon-ID zu finden.

Sobald Sie die ID haben, können Sie sie mit einem verwenden order by. Durch nulls firstdie Verwendung werden die Produkte an die Spitze gesetzt, die keine Amazon-ID haben.

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;

Online-Beispiel

---

Mit Postgres 12 wäre dies etwas kürzer:

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

Answer 2

Nach ein paar Änderungen ist dies die Abfrage, die es endlich geschafft hat,

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