wake-up-neo.net

Abfrage von Arrayelementen innerhalb des JSON-Typs

Ich versuche, den Typ json in PostgreSQL 9.3 zu testen.
Ich habe eine json-Spalte mit dem Namen data in einer Tabelle mit dem Namen reports. Die JSON sieht ungefähr so ​​aus:

{
  "objects": [
    {"src":"foo.png"},
    {"src":"bar.png"}
  ],
  "background":"background.png"
}

Ich möchte die Tabelle nach allen Berichten abfragen, die mit dem Wert 'src' im Array 'objects' übereinstimmen. Ist es beispielsweise möglich, die Datenbank nach allen Berichten abzufragen, die mit 'src' = 'foo.png' übereinstimmen? Ich habe erfolgreich eine Abfrage geschrieben, die mit dem "background" übereinstimmen kann:

SELECT data AS data FROM reports where data->>'background' = 'background.png'

Aber da "objects" ein Array von Werten hat, kann ich anscheinend nicht etwas schreiben, das funktioniert. Ist es möglich, die Datenbank nach allen Berichten abzufragen, die mit 'src' = 'foo.png' übereinstimmen? Ich habe diese Quellen durchgesehen, kann es aber immer noch nicht verstehen:

Ich habe auch solche Dinge ausprobiert, aber ohne Erfolg:

SELECT json_array_elements(data->'objects') AS data from reports
WHERE  data->>'src' = 'foo.png';

Ich bin kein SQL-Experte und weiß daher nicht, was ich falsch mache.

64
pacothelovetaco

json in Postgres 9.3+

Testen Sie das JSON-Array mit der Funktion json_array_elements() in einem Lateral-Join in der FROM-Klausel, und prüfen Sie die Elemente:

WITH reports(data) AS (
   VALUES ('{"objects":[{"src":"foo.png"}, {"src":"bar.png"}]
           , "background":"background.png"}'::json)
   ) 
SELECT *
FROM   reports r, json_array_elements(r.data#>'{objects}') obj
WHERE  obj->>'src' = 'foo.png';

Die CTE (WITH-Abfrage) ersetzt lediglich eine Tabelle reports.
Oder für eine Verschachtelungsebene single gleichwertig:

SELECT *
FROM   reports r, json_array_elements(r.data->'objects') obj
WHERE  obj->>'src' = 'foo.png';

->>, -> und #> Operatoren werden im Handbuch erklärt.

Beide Abfragen verwenden ein implizites JOIN LATERAL .

SQL Geige.

Eng verwandte Antwort: 

jsonb in Postgres 9.4+

Verwenden Sie das Äquivalent jsonb_array_elements() .

Besser noch, verwenden Sie den neuen "enthält" Operator @> (am besten in Kombination mit einem entsprechenden GIN-Index für den Ausdruck data->'objects'):

CREATE INDEX reports_data_gin_idx ON reports
USING gin ((data->'objects') jsonb_path_ops);

SELECT * FROM reports WHERE data->'objects' @> '[{"src":"foo.png"}]';

Da der Schlüssel objects ein JSON array enthält, müssen wir die Struktur im Suchbegriff anpassen und das Array-Element ebenfalls in eckige Klammern einschließen. Löschen Sie die Array-Klammern, wenn Sie einen einfachen Datensatz suchen.

Ausführliche Erklärung und weitere Optionen:

148

Erstellen Sie eine Tabelle mit Spalte als Typ Json

# CREATE TABLE friends ( id serial primary key, data jsonb);

Nun fügen wir Json-Daten ein

# INSERT INTO friends(data) VALUES ('{"name": "Arya", "work": ["Improvements", "Office"], "available": true}');

# INSERT INTO friends(data) VALUES ('{"name": "Tim Cook", "work": ["Cook", "ceo", "Play"], "uses": ["baseball", "laptop"], "available": false}');

Lassen Sie uns nun einige Abfragen machen, um Daten abzurufen

# select data->'name' from friends;

# select data->'name' as name, data->'work' as work from friends;

Möglicherweise haben Sie bemerkt, dass die Ergebnisse mit invertiertem Komma (") und Klammern ([]) angezeigt werden.

    name    |            work            
------------+----------------------------
 "Arya"     | ["Improvements", "Office"]
 "Tim Cook" | ["Cook", "ceo", "Play"]
(2 rows)

Um nur die Werte abzurufen, verwenden Sie einfach ->> 

# select data->>'name' as name, data->'work'->>0 as work from friends;

#select data->>'name' as name, data->'work'->>0 as work from friends where data->>'name'='Arya';
0
Sandip Debnath