Konkretes Szenario mit JSON

Hallo,
wolte heute JSON mit PostgreSQL ausprobieren.
Dafür habe eine Tabelle erstellt und mit Daten befüllt.
Der Inhalt der Spalten “inhalt und inhalt_json” ist valide JSON-Format.
Die Abfragen mit der Spalte “inhalt_json” funktionieren.

SELECT inhalt_json->>‘Hobbys’ FROM jsontest WHERE ID = 2
SELECT * FROM json_each_text((SELECT inhalt_json FROM jsontest where ID=2));

Die gleichen Ergebnisse will ich durch SELECT(siehe oben) an der Spalte “inhalt” des Text-Datentyps erreichen.
Der Grund wieso ich den Datentyp Text und nicht JSON genommen habe => nicht alle ORMs unterstützen den JSON- oder hstore-Datentyp.
Mein Gedanke war den Inhalt der Spalte “inhalt” mit der Methode to_json zu konvertieren und das Zwischenerbebnis im Select weiter zu verwenden, aber es geht irgendwie nicht.

select to_json(inhalt) from jsontest;
Ich komme nicht weiter.


Geht es überhaupt ohne ein JSON-Datentyp zu verwenden?
Wie kann man dies erreichen?
Hat jemand Erfahrung damit gemacht?
Habt ihr ein paar konkrete Beispiele für mich?




CREATE TABLE jsontest
(
id serial NOT NULL,
inhalt text,
inhalt_json json,
CONSTRAINT jsontest_pkey PRIMARY KEY (id)
)

insert into jsontest (inhalt, inhalt_json) Values(’{“Name”:“Person1”,“Vorname”:“John”,“Hobbys”:[“Musik”,“Sport”,“Angeln”,“Jagen”]}’, ‘{“Name”:“Person1”,“Vorname”:“John”,“Hobbys”:[“Musik”,“Sport”,“Angeln”,“Jagen”]}’);
insert into jsontest (inhalt, inhalt_json) Values(’{“Name”:“Person2”,“Vorname”:“Jürgen”,“Hobbys”:[“Sport”,“Jagen”]}’, ‘{“Name”:“Person2”,“Vorname”:“Jürgen”,“Hobbys”:[“Sport”,“Jagen”]}’);
insert into jsontest (inhalt, inhalt_json) Values(’{“Name”:“Person3”,“Vorname”:“Viktor”,“Hobbys”:[“Sport”]}’, ‘{“Name”:“Person3”,“Vorname”:“Viktor”,“Hobbys”:[“Sport”]}’);

Hilft

http://wiki.postgresql.org/wiki/What's_new_in_PostgreSQL_9.2#JSON_datatype

weiter?

Sorry, das hilft mir nicht weiter.
Wenn ich diese Abfrage für den json-Typ ausführe, dann kriege ich diese Werte

Abfrage: SELECT inhalt_json->>‘Hobbys’ FROM jsontest WHERE ID = 2
Werte: “[“Sport”,“Jagen”]”

Wie soll dann Select Abfrage für die Spalte “inhalt” mit demText-Datentyp aufgebaut werden, damit ich die gleichen Werte erhalte???
Ich habe diese ausprobiert, so ist es aber falsch.

SELECT inhalt->>‘Hobbys’ FROM jsontest WHERE ID = 2

FEHLER: Operator existiert nicht: text ->> unknown
SQL Status:42883
Hinweis:Kein Operator stimmt mit dem angegebenen Namen und den Argumenttypen überein. Sie müssen möglicherweise ausdrückliche Typumwandlungen hinzufügen.
Zeichen:14

So geht es auch nicht
SELECT to_json(inhalt)->>‘Hobbys’ FROM jsontest WHERE ID = 2

FEHLER: kann kein Element aus einem skalaren Wert auswählen
SQL Status:22023

Datensätze in der DB

ID:Serial inhalt:Text inhalt_json: json
1;“{“Name”:“Person1”,“Vorname”:“John”,“Hobbys”:[“Musik”,“Sport”,“Angeln”,“Jagen”]}”;“{“Name”:“Person1”,“Vorname”:“John”,“Hobbys”:[“Musik”,“Sport”,“Angeln”,“Jagen”]}”
2;“{“Name”:“Person2”,“Vorname”:“Jürgen”,“Hobbys”:[“Sport”,“Jagen”]}”;“{“Name”:“Person2”,“Vorname”:“Jürgen”,“Hobbys”:[“Sport”,“Jagen”]}”
3;“{“Name”:“Person3”,“Vorname”:“Viktor”,“Hobbys”:[“Sport”]}”;“{“Name”:“Person3”,“Vorname”:“Viktor”,“Hobbys”:[“Sport”]}”

Ahh okay.

2 Spalten mit demselben Inhalt iast was? Richtig: Bullshit.

Um Dir zu zeigen, wie es geht:

test=*# \d jsontest;
                            Table "public.jsontest"
   Column    |  Type   |                       Modifiers
-------------+---------+-------------------------------------------------------
 id          | integer | not null default nextval('jsontest_id_seq'::regclass)
 inhalt      | text    |
 inhalt_json | json    |
Indexes:
    "jsontest_pkey" PRIMARY KEY, btree (id)

test=*# select * from jsontest;
 id |                                     inhalt                                      |                                   inhalt_json
----+---------------------------------------------------------------------------------+-----------------------------------------------------------------------
  7 | {"Name":"Person1","Vorname":"John","Hobbys":["Musik","Sport","Angeln","Jagen"]} | {"Name":"Person1","Vorname":"John","Hobbys":["Musik","Sport","Angeln",
  8 | {"Name":"Person2","Vorname":"Jürgen","Hobbys":["Sport","Jagen"]}                | {"Name":"Person2","Vorname":"Jürgen","Hobbys":["Sport","Jagen"]}
  9 | {"Name":"Person3","Vorname":"Viktor","Hobbys":["Sport"]}                        | {"Name":"Person3","Vorname":"Viktor","Hobbys":["Sport"]}
(3 rows)

Time: 0,188 ms
test=*# SELECT (inhalt::json)->>'Hobbys' FROM jsontest WHERE ID = 7;
              ?column?
------------------------------------
 ["Musik","Sport","Angeln","Jagen"]
(1 row)

(spalte::datentyp) macht Dir eine Konvertierung, falls möglich. Du könntest auch mit

test=*# select inhalt_json::text from jsontest where id = 7;
                                   inhalt_json
---------------------------------------------------------------------------------
 {"Name":"Person1","Vorname":"John","Hobbys":["Musik","Sport","Angeln","Jagen"]}
(1 row)

Dir die json-Spalte als Text ausgeben lassen.

Was nicht geht:

test=*# select (inhalt_json::text)->>'Hobbys' from jsontest where id = 7;
ERROR:  operator does not exist: text ->> unknown
LINE 1: select (inhalt_json::text)->>'Hobbys' from jsontest where id...
                                  ^
HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.
Time: 0,226 ms

Und weil es grad so Spaß macht:

test=*# select ((((((inhalt_json::text)::json)::text)::json)::text)::json)->>'Hobbys' from jsontest where id = 7;
              ?column?
------------------------------------
 ["Musik","Sport","Angeln","Jagen"]
(1 row)

und wenn sie nicht gestorben sind, so casten sie noch heute.

Tabelle mit 2 Spalten mit demselben Inhalt ist nur eine Tabelle, die ich zum Testen erstellt habe.
Wollte sehen wie sich die Abfragen mit unterschiedlichen Datentypen verhalten und wie man sie richtig absetzt.

In dieser Spalte verbirgt sich die Antwort auf meine Frage :slight_smile: :smiley:

test=*# SELECT (inhalt**::json**)->>‘Hobbys’ FROM jsontest WHERE ID = 7;

Vielen Dank für so eine detaillierte und schnelle Antwort.

Noch eine kleine Frage.

Wie kann ich einen GIN-Index anlegen.
Ich habe so versucht aber da kriege ich die Fehlermeldung.

FEHLER: Funktionen im Indexausdruck müssen als IMMUTABLE markiert sein

CREATE INDEX GIN_NAME ON jsontest USING GIN(to_tsvector(inhalt_json->>‘Name’));

Was heiß es genau “unverändert”??
Habe ich einen weiteren Ausdruck vergessen?

Unverändert heiß IMMUTABLE :wink:


Also, damit ein Index auf dem Resultat einer Funktion erstellt werden kann, muß diese Funktion immer und unter allen Bedingungen bei selben Eingabewerten das selbe Resultat liefern. Das z.B. eine Zufallsfunktion dies nicht sein kann ist also klar. Offenbar ist auch to_tsvector() nicht dafür ausgelegt, da man über Config-Änderungen Einfluß auf das Resultat nehmen kann. Ich bin aber jetzt nicht sonderlich firm mit den Textsearch-Kram.