JSON in Postgres speichern und wieder über Select auswerten

Hi,

ich lese immer wieder, dass man im Fall von JSON doch lieber statt auf MongoDB doch auf die Postgres gehen soll. Da ich nicht so tief im Postgres-Thema bin habe ich hier folgende Frage. Wäre toll, wenn mir hier jemand etwas dazu sagen könnte.

Folgendes Szenario:
JSON-Daten werden via Python in Postgres gepumpt. Dabei werden die JSON-Daten in ein einzelnes Feld vom Typ json geschrieben.
Anschließend sollen die Daten mit einem Frontend-Tool (Tableau) ausgewertet werden.

Nach meinen ersten Tests ist es aber so, dass ein normaler Select auf die Tabelle natürlich auch //nur// die normalen Felder herausbringt und natürlich die JSON-Datenfelder nicht aufschlüsselt.

Gibt es eine Möglichkeit, mit einem Select oder einer anderen Möglichkeit, die Daten aus einem JSON-Feld in einzelnen Spalten sichtbar zu machen? Erste Ebene wäre erst einmal total ausreichend…

Viele Grüße
Stephan

ja, geht.

test=*# create table kunden(id serial primary key, info jsonb, check ((info ->> 'name'::text) is not null));
CREATE TABLE
test=*# insert into kunden (info) values ('{"name": "Kretschmer", "email": "andreas.kretschmer@2ndquadrant.com", "phone": "035204 278477"}');
INSERT 0 1
test=*# insert into kunden (info) values ('{"name": "Mustermann", "email": "max.mustermann@example.com"}');
INSERT 0 1
test=*# select * from kunden;
 id |                                              info                                               
----+-------------------------------------------------------------------------------------------------
  1 | {"name": "Kretschmer", "email": "andreas.kretschmer@2ndquadrant.com", "phone": "035204 278477"}
  2 | {"name": "Mustermann", "email": "max.mustermann@example.com"}
(2 Zeilen)

test=*# select k.id, i.name, i.email, i.phone from kunden k join lateral (select * from jsonb_to_record(info) as detail(name text, email text, phone text)) i on (true);
 id |    name    |               email                |     phone     
----+------------+------------------------------------+---------------
  1 | Kretschmer | andreas.kretschmer@2ndquadrant.com | 035204 278477
  2 | Mustermann | max.mustermann@example.com         | 
(2 Zeilen)

test=*# create index idx_kunden_info on kunden using gin (info);
CREATE INDEX
test=*# explain select * from kunden where info ? 'phone';
                                  QUERY PLAN                                  
------------------------------------------------------------------------------
 Bitmap Heap Scan on kunden  (cost=8.00..12.01 rows=1 width=36)
   Recheck Cond: (info ? 'phone'::text)
   ->  Bitmap Index Scan on idx_kunden_info  (cost=0.00..8.00 rows=1 width=0)
         Index Cond: (info ? 'phone'::text)
(4 Zeilen)

test=*#

Beachte, daß es JSON und JSONB gibt, das B steht für Binary. JSONB-Daten können besser indiziert werden. Du kannst (wie auch angedeutet) dann direkt in den JSONB-Daten suchen/filtern. Es gibt einiges an Doku dazu, google kann da helfen …