wake-up-neo.com

Abfrage eines Array-Elements in der JSON-Spalte

Kürzlich wurde ein Upgrade auf PostgreSQL 9.3.1 vorgenommen, um die JSON-Funktionen zu nutzen. In meiner Tabelle habe ich eine Json-Typ-Spalte, die folgende Struktur hat:

{
   "id": "123",
   "name": "foo",
   "emails":[
      {
        "id": "123",
        "address": "somethinghere"
      },
      {
        "id": "456",
        "address": "soemthing"
      }
   ]
}

Dies sind nur Scheindaten für den Zweck der Frage.

Ist es möglich, nach einem bestimmten Element im E-Mail-Array basierend auf der ID abzufragen?
Ziemlich: "E-Mail zurückgeben wo id = 123)"?

sqljsonpostgresqlpostgresql-9.3lateral
21
Joe

Ja das ist möglich:

SELECT *
FROM   tbl t, json_array_elements(t.json_col->'emails') AS elem
WHERE  elem->>'id' = 123;

tbl ist Ihr Tabellenname, json_col ist der Name der JSON-Spalte.

Weitere Details in dieser verwandten Antwort:

Mehr zum impliziten CROSS JOIN LATERAL im letzten Absatz dieser Antwort:

Index zur Unterstützung dieser Art von Abfrage:

49
Erwin Brandstetter

Mit einer JSONB-Spalte in Postgres 9.4+ können Sie den Enthält-Operator @> verwenden, um ein Element in einem Array abzufragen:

SELECT * FROM jsontest WHERE data @> '{ "emails": [{ "id": "123" }] }';

Weitere Informationen finden Sie in Abfrage für Arrayelemente in JSON-Typ .

Hier ist ein Arbeitsbeispiel:

CREATE TABLE jsontest(data JSONB NOT NULL);
INSERT INTO jsontest VALUES (
  '{
     "name": "foo",
     "id": "123",
     "emails": 
     [
       {
         "address": "somethinghere",
         "id": "123"
       },
       {
         "address": "soemthing",
         "id": "456"
       }
     ]
  }'
);
SELECT * FROM jsontest WHERE data @> '{ "emails": [{ "id": "123" }] }';

data
----
{"id": "123", "name": "foo", "emails": [{"id": "123", "address": "somethinghere"}, {"id": "456", "address": "soemthing"}]}

(1 Reihe)

7
adamc

Kam durch diesen Beitrag und stellte fest, dass Sie die Tabelle direkt wie folgt abfragen können:

SELECT *
FROM   table_name, json_array_elements(json_column) AS data
WHERE  data->>'id' = 123;

Diesen Teil auslassen:

json_array_elements(t.json_col->'emails')
2
Deepak Mahakale

Sie können es so einfach tun wie: 

SELECT * FROM table WHERE emails->>'id' = '123';

es scheint, dass Sie die ID als String speichern. Wenn es sich um eine Ganzzahl handelt, können Sie dies folgendermaßen tun: 

SELECT *  from table WHERE cast(emails->>'id' as integer ) = 123  ;

oder Sie können alle Zeilen mit der ID> 10 erhalten

SELECT *  from table WHERE cast(emails->>'id' as integer ) > 10  ;
0
Yunis Hawwash