Abfrage nach Array-Elementen innerhalb des JSON-Typs

Ich versuche zu testen, die json Typ in PostgreSQL 9.3.
Ich habe eine json Spalte namens data in einer Tabelle namens reports. Das JSON sieht wie folgt aus:

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

Ich würde gerne die Abfrage der Tabelle, die für alle Berichte, die mit dem 'src' Wert in den 'objects' array. Zum Beispiel ist es möglich, die Abfrage der DB für alle Berichte, die übereinstimmung 'src' = 'foo.png'? Ich erfolgreich schrieb eine Abfrage, kann sich mit der "background":

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

Aber da "objects" hat ein array von Werten, ich kann nicht scheinen, etwas zu schreiben, das funktioniert. Ist es möglich, die Abfrage der DB für alle Berichte, die übereinstimmung 'src' = 'foo.png'? Ich habe mir durch diese Quellen aber kann immer noch nicht bekommen:

Ich habe auch versucht Dinge wie diese, aber ohne Erfolg:

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

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

InformationsquelleAutor der Frage pacothelovetaco | 2014-03-29

  1. 126

    json in Postgres 9.3+

    Unnest die JSON-array mit der Funktion json_array_elements() in eine seitliche Verknüpfung in der FROM - Klausel und test für 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';

    Den WAK (WITH Abfrage) nur Ersatz für eine Tabelle reports.

    Oder, gleichwertig, nur für einen single Ebene der Verschachtelung:

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

    ->>-> und #> - Operatoren sind in der Anleitung erklärt.

    Beide Abfragen, verwenden Sie eine implizite JOIN SEITLICHEN.

    SQL Fiddle.

    Eng verwandt Antwort:

    jsonb in Postgres 9.4+

    Verwenden Sie die entsprechende jsonb_array_elements().

    Besser noch, verwenden Sie die neue "enthält" - operator @> (am besten in Kombination mit einem passenden GIN-index auf die expression 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 hält ein JSON - arraymüssen wir mit der Struktur in den Suchbegriff ein und wickeln Sie die array-Elemente in eckigen Klammern, zu. Fallen die array-Klammern bei der Suche nach einem einfachen Datensatz.

    Ausführliche Erklärung und weitere Optionen:

    InformationsquelleAutor der Antwort Erwin Brandstetter

  2. 1

    Erstellen Sie eine Tabelle mit Spalte als Typ json

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

    Nun fügen Sie json-Daten

    # 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}');

    Nun lassen Sie uns einige Abfragen zum abrufen von Daten

    # select data->'name' from friends;

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

    Haben Sie vielleicht bemerkt, dass die Ergebnisse kommt mit Hochkomma( " ) und eckigen Klammern ([ ])

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

    Nun nur abrufen der Werte benutzen 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';

    InformationsquelleAutor der Antwort Sandip Debnath

Schreibe einen Kommentar