JSON in Postgres speichern und wieder über Select auswerten

Fragen und Probleme rund um die Abfragespache SQL.
Antworten
sbreiden
Neuer Benutzer
Beiträge: 1
Registriert: Fr 11. Mai 2018, 08:30

JSON in Postgres speichern und wieder über Select auswerten

Beitrag von sbreiden » Fr 11. Mai 2018, 08:49

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

akretschmer
Erfahrener Benutzer
Beiträge: 7426
Registriert: So 29. Okt 2006, 13:35
Wohnort: Kaufbach, Sachsen, Germany, Germany
Kontaktdaten:

Re: JSON in Postgres speichern und wieder über Select auswerten

Beitrag von akretschmer » Fr 11. Mai 2018, 10:07

ja, geht.

Code: Alles auswählen

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 ...
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Antworten