Postgresql: Sie Finden die Werte im JSON-array als Platzhalter und Vergleichsoperatoren mit index

Ich habe eine Tabelle mit JSON-array-Daten möchte ich suchen. 

CREATE TABLE data (id SERIAL, json JSON);

INSERT INTO data (id, json) 
  VALUES (1, '[{"name": "Value A", "value": 10}]');

INSERT INTO data (id, json) 
  VALUES (2, '[{"name": "Value B1", "value": 5}, {"name": "Value B2", "value": 15}]');

Wie beschrieben in diese Antwort, ich habe eine Funktion, die es erlaubt, auch erstellen Sie einen index für das array Daten (wichtig).

CREATE OR REPLACE FUNCTION json_val_arr(_j json, _key text)
  RETURNS text[] AS
$$
SELECT array_agg(elem->>_key)
FROM   json_array_elements(_j) AS x(elem)
$$
  LANGUAGE sql IMMUTABLE;

Dies funktioniert gut, wenn ich will, zu finden, die einen gesamten Wert (zB. "Wert B1"):

SELECT *
FROM data
WHERE '{"Value B1"}'::text[] <@ (json_val_arr(json, 'name'));

Nun meine Fragen:

  1. Ist es möglich, Werte mit einem Platzhalter (zB. "Wert*")? So etwas wie die folgenden (naiven) Ansatz:

    ...
WHERE '{"Value%"}'::text[] <@ (json_val_arr(json, 'name'));

  2. Ist es möglich, numerische Werte mit Vergleichsoperatoren (zB. >= 10)? Wieder ein naiver und offensichtlich falsche Ansatz:

    ...
WHERE '{10}'::int[] >= (json_val_arr(json, 'value'));

    Ich habe versucht, zu erstellen eine neue Funktion Rückkehr int[] aber das hat nicht funktioniert.

Erstellte ich eine SQL Fiddle zu verdeutlichen mein problem.

Oder wäre es besser einen anderen Ansatz wie die folgenden Werktag Abfragen:

SELECT *
FROM data,
   json_array_elements(json) jsondata
WHERE jsondata ->> 'name' LIKE 'Value%';

und

...
WHERE cast(jsondata ->> 'value' as integer) <= 10;

Jedoch, für diese Abfragen, ich war nicht in der Lage für die Erstellung eines index wurde tatsächlich abgeholt von den Abfragen.

Auch, würde ich mag, um alle implementieren diese in Postgresql 9.4 mit JSONB irgendwann, aber ich denke, für die oben genannten Fragen sollte dies kein Problem sein.

Vielen Dank!

InformationsquelleAutor lukas | 2014-07-31
  1. 8

    Ich weiß, es ist schon eine Weile her, aber ich war einfach tuckern auf etwas ähnliches (mit wild cards Abfrage von json-Datentypen) und dachte, ich würde teilen, was ich gefunden habe.

    Erstens, das war ein großer Punkt in die richtige Richtung:
    http://schinckel.net/2014/05/25/querying-json-in-postgres/

    Take-away ist, dass die Methode für die Auflösung der json-element in etwas anderes (ein record-set) ist der Weg zu gehen. Es ermöglicht die Abfrage des json-Elemente mit normaler postgres Zeug.

    In meinem Fall:

    #Table:test
    ID | jsonb_column
     1 | {"name": "", "value": "reserved", "expires_in": 13732}
     2 | {"name": "poop", "value": "{\"ns\":[\"Whaaat.\"]}", "expires_in": 4554} 
     3 | {"name": "dog", "value": "{\"ns\":[\"woof.\"]}", "expires_in": 4554}

    Beispiel Abfrage

    select * from test jsonb_to_recordset(x) where jsonb_column->>'name' like '%o%';

# => Returns
# 2 | {"name": "poop", "value": "{\"ns\":[\"Whaaat.\"]}", "expires_in": 4554}

    Und zu Ihrer Frage, jsonb: Es sieht aus wie jsonb ist die bessere route die MEISTE Zeit. Es hat mehrere Methoden und schneller Lesen (aber langsamer schreiben) mal.

    Quellen:

    Happy hunting!

    • Was ist das x bezieht sich das? Kann nicht herausfinden, wie man diese Abfrage ausführen
    • Nicht sicher, was die x ist, allerdings ist dies für mich gearbeitet: SELECT COUNT(*) FROM people, jsonb_to_record(people.data) AS x(title text) WHERE x.title LIKE '%Police%'" Vorausgesetzt, die people.data ist ein jsonb Spalte und hat eine Taste mit der Bezeichnung Titel. Beachten Sie, dass es den ausbau zu einem Datensatz nicht recordset.
    InformationsquelleAutor William Dembinski
Schreibe einen Kommentar