JSON-Datenformat in PostgreSQL Verwenden

Json-datenformat in postgresql verwenden

Ein Beispiel mit Wetterdaten, die von der API bei OpenWeatherMap zur Verfügung gestellt wurden.

Einleitung

Du wolltest schon immer mal wissen, wie man die vielseitige Funktionalität von PostgreSQL nutzen kann, um mit JSON-Objekten zu arbeiten? Dann bist du hier genau richtig! Ursprünglich wurde das Feature in PostgreSQL Version 9.2 eingeführt und in Version 9.3 stark verbessert. Wir gehen also von einer installierten Version von PostgreSQL 9.3 oder höher aus und sehen uns die Operatoren an, die mit dem JSON Datenformat geliefert werden. Wie genau du damit arbeiten kannst, dass zeigen wir dir hier. Zudem gibt noch einen weiteres neues Datenformat JSONb, das in der Version 9.4.2 eingeführt wurde.

NEUHEITEN

Der Hauptaspekt der JSON-Unterstützung ist das neue Datenformat und seine zusätzlichen Funktionen und Operatoren. Diese bilden eine starke Säule für die typische Arbeit mit JSON-Objekten und deshalb werden wir uns hauptsächlich auf sie konzentrieren. Zunächst die wichtigsten Neuerungen:

  • Neues Datenformat: json
  • Neue Operatoren: ->, ->>, #> and #>>
  • Neue Funktionen: json_array_length, json_extract_path, json_array_elements und viele mehr

Die vollständige Liste der JSON-spezifischen Funktionen findest du unter http://www.postgresql.org/docs/9.3/static/functions-json.html.

ALLGEMEINE VERWENDUNG DER OPERATOREN

Beginnen wir mit einem kurzen Überblick über die Hauptnutzung und die Unterschiede der drei Operatoren. Anschließend schauen wir uns ihre Verwendung einmal genauer an. Die Operatoren -> and ->> sind einfach zu bedienen und erlauben es, auf ein bestimmtes Child-Element innerhalb eines JSON-Objekts zu verweisen. Beide Operatoren können verwendet werden,

  1. indem man entweder den Namen des Elements benutzt, das von Interesse ist oder
  2. indem man einen Index verwendet, wenn das aktuelle Objekt ein Array von Elementen ist.

Es ist auch möglich, einen dynamischen Pfad innerhalb eines JSON-Objekts anzugeben, den wir spezifizieren wollen. Zu diesem Zweck wurden die Operatoren #> and #>>> hinzugefügt. In Bezug auf den resultierenden Datentyp, können wir sagen #> gleich -> und  #>> gleich ->>.

Bevor wir zu den Beispielen kommen, hier eine Liste der wichtigsten Aspekte der Operatoren:

Operator ->

  • Ermöglicht die Auswahl eines Elements anhand seines Namens.
  • Ermöglicht die Auswahl eines Elements innerhalb eines Arrays basierend auf seinem Index.
  • Kann sequentiell verwendet werden: ::json->'elementL'->'subelementM'->...->'subsubsubelementN'.
  • Der Rückgabetyp ist JSON und das Ergebnis kann nicht mit Funktionen und Operatoren verwendet werden, die einen string-basierten Datentyp benötigen. Aber das Ergebnis kann mit Operatoren und Funktionen verwendet werden, die JSON-Datentypen benötigen.

Operator ->>>

  • Ermöglicht die Auswahl eines Elements anhand seines Namens.
  • Ermöglicht die Auswahl eines Elements innerhalb eines Arrays basierend auf seinem Index.
  • Kann nicht sequentiell verwendet werden.
  • Der Rückgabetyp ist Text und das Ergebnis kann mit Funktionen und Operatoren verwendet werden, die einen string-basierten Datentyp benötigen. Aus dem gleichen Grund wird die sequentielle Nutzung des Operators nicht unterstützt.

Operator #>

  • Ermöglicht die Auswahl eines Elements anhand seines Pfades innerhalb des JSON-Hauptobjekts. Der Pfad kann aus Elementnamen und Array-Indizes bestehen, je nachdem, was benötigt wird.
  • Kann sequentiell verwendet werden: ::json#>'{elementname1,elementname2,index1,index2}'#>'{elementname3}''.
  • Der Rückgabetyp ist JSON und das Ergebnis kann nicht mit Funktionen und Operatoren verwendet werden, die einen string-basierten Datentyp benötigen. Aber das Ergebnis kann mit Operatoren und Funktionen verwendet werden, die den JSON-Datentyp benötigen.

Operator #>>>>

  • Ermöglicht die Auswahl eines Elements anhand seines Pfades innerhalb des JSON-Hauptobjekts. Der Pfad kann aus Elementnamen und Array-Indizes bestehen, je nachdem, was benötigt wird.
  • Kann nicht sequentiell verwendet werden.
  • Der Rückgabetyp ist Text und das Ergebnis kann mit Funktionen und Operatoren verwendet werden, die einen string-basierten Datentyp benötigen. Aus dem gleichen Grund wird die sequentielle Nutzung des Operators nicht unterstützt.

Grundsätzlich kannst du eine Kette von -> und #> Operatoren erstellen, vorausgesetzt, du zeigst auf gültige Elemente und Indizes. Eine solche Kette kann mit jedem der vier Operatoren enden. Diese bestimmen, ob das Ergebnis als Eingabe für andere spezifische Funktionen verwendet werden kann. Beachte, dass du als letztes in der Kette ->> oder #>>> verwenden musst, wenn du das Ergebnis in Kombination mit einer Funktion oder einem Operator verwenden möchtest, der einen Text-Datentyp benötigt. Dies kann wichtig sein, wenn du eine WHERE-Klausel oder eine Sub-Anweisung hast, die sich auf das Ergebnis der Json-Operatoren bezieht. Lass uns mit einigen Beispielen fortfahren, um diese Operatoren in Aktion zu sehen.

Manchmal sehe ich ::json und manchmal nicht, was ist der Unterschied?

Das ist einfach. Die Operatoren ->, ->>, #>, #> und #>>> benötigen den Eingabe-Datentyp JSON. Wenn PostgreSQL den Parameter implizit zu JSON konvertieren kann, dann kannst du ::json weglassen. Wenn du nicht garantieren kannst, dass die Typumwandlung immer durchgeführt wurde, kannst du ::json angeben, um die Operatoren zu nutzen. Verwende im Zweifelsfall das Präfix ::json vor dem ersten JSON-Operator. Die darauf folgenden Operatoren benötigen diese Syntax nicht, wenn du sie verkettest.

QUERY-BEISPIELE

Einfügen von Beispieldaten in eine neue Datenbank

Zunächst einmal benötigen wir einige Daten, die wir für unser Beispiel nutzen können. Wir verwenden Wetterdaten, die von der API bei OpenWeatherMap zur Verfügung gestellt wurden. Diese Daten sind komplex genug und sind bestens geeignet, um sich grundlegend mit den Operatoren vertraut zu machen. Um Zugang zur Wetter-API zu erhalten, klicke einfach auf den Link in der nächsten Zeilen und erstelle dir deinen API-Key-Wetter-Account: https://home.openweathermap.org/users/sign_up Wir interessieren uns für die bekanntesten englischen Städte: London (Big Ben), Sheffield (Snooker Weltmeisterschaft), Liverpool (Beatles und Mersey Beat Generation), Manchester (für die Fußballfans) und Leicester (der köstliche Red-Leicester-Käse). Wenn du die gleichen Daten wie in diesem Artikel verwenden willst, kannst du die folgenden SQL-Anweisungen ausführen. Bitte erstelle zunächst eine Datenbank zum Testen. Der Beitrag geht davon aus, dass dies bereits geschehen ist. Eine alternative Möglichkeit wird nach den Statements dargestellt, welche die neue Tabelle erzeugt und in einem Schritt mit Daten füllt.


CREATE TABLE public.json_table (myjson json);
INSERT INTO public.json_table VALUES('{"coord":{"lon":-0.13,"lat":51.51},"sys": {"type":3,"id":98614,"message":0.0218,"country":"GB","sunrise":1427693969,"sunset":1427740219},"weather": [{"id":803,"main":"Clouds","description":"broken clouds","icon":"04d"}],"main": {"temp":6.99,"humidity":57,"pressure":1014.9,"temp_min":5.56,"temp_max":10.3},"wind": {"speed":0.7,"gust":1.7,"deg":315},"rain":{"3h":0},"clouds": {"all":64},"dt":1427699997,"id":2643743,"name":"London"}');
INSERT INTO public.json_table VALUES('{"coord":{"lon":-1.47,"lat":53.38},"sys":{"type":3,"id":38764,"message":0.0121,"country":"GB","sunrise":1427694192,"sunset":1427740640},"weather":[{"id":802,"main":"Clouds","description":"scattered clouds","icon":"03d"}],"main":{"temp":4.64,"humidity":75,"pressure":988,"temp_min":4.44,"temp_max":5},"wind":{"speed":3.08,"gust":7.71,"deg":226},"rain":{"3h":0.03},"clouds":{"all":36},"dt":1427699954,"id":2638077,"name":"Sheffield"}');
INSERT INTO public.json_table VALUES('{"coord":{"lon":-2.98,"lat":53.41},"sys":{"type":3,"id":10567,"message":0.0519,"country":"GB","sunrise":1427694552,"sunset":1427741005},"weather":[{"id":800,"main":"Clear","description":"Sky is Clear","icon":"01d"}],"main":{"temp":8,"humidity":67,"pressure":1000,"temp_min":3.33,"temp_max":17.78},"wind":{"speed":4.63,"gust":7.2,"deg":282},"rain":{"3h":0},"clouds":{"all":0},"dt":1427700074,"id":2644210,"name":"Liverpool"}');
INSERT INTO public.json_table VALUES('{"coord":{"lon":-2.24,"lat":53.48},"sys":{"type":3,"id":28022,"message":0.0377,"country":"GB","sunrise":1427694371,"sunset":14277408
31},"weather":[{"id":801,"main":"Clouds","description":"few clouds","icon":"02d"}],"main":{"temp":4.66,"pressure":1004,"temp_min":3.33,"temp_max":6.11,"humidity":89},"wind":{"speed":3.6,"gust":5.65,"deg":268},"rain":{"3h":0.0099999999999998},"clouds":{"all":24},"dt":1427700074,"id":2643123,"name":"Manchester"}');
INSERT INTO public.json_table VALUES('{"coord":{"lon":-1.13,"lat":52.64},"sys":{"type":3,"id":184635,"message":0.9267,"country":"GB","sunrise":1427694152,"sunset":1427740519},"weather":[{"id":802,"main":"Clouds","description":"scattered clouds","icon":"03d"}],"main":{"temp":4.92,"humidity":75,"pressure":1013,"temp_min":4.4,"temp_max":5.4},"wind":{"speed":2.9,"gust":7.5,"deg":337},"rain":{"3h":0.024999999999999},"clouds":{"all":36},"dt":1427700055,"id":2644668,"name":"Leicester"}');

AKTUELLE DATEN EINFÜGEN MIT SELECT IN

Hole dir deine persönlichen Daten von dieser URL, nachdem du einen API-Key-Wetter-Account erstellt hast und kopiere das Ergebnis in einen SQL-Editor von pgAdmin. Du kannst die volle JSON einfügen und die Funktion json_array_elements verwenden. 

SELECT value as myjson INTO public.json_table FROM json_array_elements('{"cnt":5,"list":[…]}'::json->'list');

Das Statement nimmt das JSON-Objekt und extrahiert daraus das Listen-Element. Da es ein Array enthält, in dem wiederum jeder Index Informationen über eine der ausgewählten Städte enthält, können wir die vordefinierte Funktion json_array_elementsthat verwenden, die für jedes Array-Element eine Zeile zurückgibt. Durch SELECT INTO werden alle zurückgegebenen Zeilen in die neue Tabelle public.json_table eingefügt.

Beispiele in der SELECT-Klausel

Jetzt ist es an der Zeit, sich endlich einige Beispiele anzuschauen:

Beispiel SELECT

SELECT myjson::json->'name' as CityName

FROM   public.json_table;

Die Query nimmt das Objekt und sucht nach dem Elementnamen. Der Wert dieses Elements wird für jede Zeile der Tabelle zurückgegeben. Beachte, dass im Beispiel das Spalten-Casting auf den Datentyp JSON durchgeführt wird.

Beispiel SELECT mit zurückgegebenem "sub" JSON.

SELECT myjson::json->'name' as CityName, myjson::json->'coord' as Coordinates

FROM   public.json_table;

Etwas komplexer wird es, wenn wir zwei Elemente, die beide über -> Operatoren und ihre Namen referenziert werden, aus der Tabelle auswählen. Beachte, dass die zweite Spalte der Ausgabe selbst ein JSON-Objekt ist, auf das wir im nächsten Beispiel eingehen werden.

Beispiel SELECT mit den "sub" JSON strings

SELECT myjson::json->'name' as CityName, myjson::json->'coord'->'lon' as Longitude,myjson::json->'coord'->'lat' as Latitude

FROM   public.json_table;

Das obige Beispiel zeigt die „Verkettung” des -> Operators. Die erste Verwendung hat das Präfix ::json, um sicherzustellen, dass der Text als JSON-Typ behandelt wird. Die konsekutive Verwendung von -> erlaubt es, auf ein Element zu verweisen, das sich innerhalb eines JSON-Objekts befindet, das wiederum selbst innerhalb eines Superobjekts existiert. Bitte beachte, dass die zweite Verwendung von -> kein vorhergehendes ::json aufweist

Beispiel SELECT, das ein Array zurückgibt

Als nächstes werden wir ein bestimmtes Array-Element innerhalb eines Objekts referenzieren. Betrachtet man einen Auszug aus dem Eintrag für London, so ergibt sich Folgendes

{…"weather":[{"id":803,"main":"Clouds","description":"broken clouds","icon":"04d"}],…}

Es ist offensichtlich, dass das Element „Wetter” ein Array enthält, auch wenn es nur die Länge 1 hat. Wenn wir weiter in dieses Array eindringen wollen, müssen wir angeben, dass wir an dem Array-Element,Index 0 interessiert sind (Indizes beginnen mit 0, nicht mit 1).
SELECT myjson::json->'name' as CityName, myjson::json->'weather'->0 as DetailedWeather

FROM public.json_table;

Da uns nur die Kernwetterinformationen interessieren,  wählen wir die Elemente main und description aus dem JSON-Objekt aus, das im Array-Element bei Index 0 gehalten wird.

Beispiel für die Adressierung von Elementen innerhalb dieses Arrays

SELECT myjson::json->'name' as CityName, myjson::json->'weather'->0->'main' as WeatherShort, myjson::json->'weather'->0->'description' as WeatherLong

FROM public.json_table;

Das obige Beispiel zeigt auch, wie die Kombination von Elementnamen oder Indizes verwendet werden kann, um zu jedem Teil eines JSON-Objekts zu navigieren. Wenn wir über Pfade sprechen, oder besser gesagt, über Pfade schreiben, können wir auch den Operator #> verwenden, um dasselbe Ziel zu erreichen. Nur, dass die Syntax etwas anders ist.

Beispiel SELECT mit spezifischem Element, das durch den Operator #> referenziert wird

SELECT myjson::json#>'{name}' as CityName,myjson::json#>'{weather,0,main}' as WeatherShort, myjson::json#>'{weather,0,description}' as WeatherLong

FROM public.json_table;

Die Ergebnisse unterscheiden sich zwar nicht, aber einige mögen es vorziehen, auf diese Weise auf verschiedene Elemente hinzuweisen. In jeder Pfadbeschreibung (begrenzt durch { und }) werden ganzzahlige Werte als Indexzahlen und Strings als Elementnamen betrachtet, wie -> und ->>.

In all diesen Beispielen hätte das letzte Auftreten von -> mit ->> ausgetauscht werden können und es wäre kein Fehler aufgetreten. Um den Unterschied zu verstehen, werden wir versuchen, die Daten in den nächsten beiden Beispielen zu filtern.

WHERE-Klauseln mit JSON

Am besten wiederholen wir einen Teil der obigen Aussage zum -> Operator: „Return-Type ist JSON und das Ergebnis kann nicht mit Funktionen und Operatoren verwendet werden, die einen string-basierten Datentyp benötigen”. Wenn wir nun also allen Städte nehmen, in denen es bewölkt ist, könnten wir es so versuchen:  Falsch

SELECT myjson::json->'name' as CityName
FROM public.json_table
WHERE myjson::json->'weather'->0->'main' = 'Clouds';

Ein Fehler wird von PostgreSQL ausgelöst: Error message:

ERROR: Operator does not: json = unknown LINE 3: WHERE myjson::json->'weather'->0->'main' = 'Clouds';

Das sagt uns, dass das Ergebnis von myjson::json->'weather'->0->'main' vom Typ json ist, aber der = Operator unterstützt JSON nicht. Ersetzen wir das letzte -> durch ->>, dann ist das Ergebnis kein JSON-Objekt, sondern ein einfacher Text, der = unterstützt. Jetzt sollte klar sein, wann ein Operator verwendet werden soll, der JSON zurückgibt, und wann ein Operator, der Text zurückgibt. Das gleiche gilt für #> und #>>, wenn wir das zurückgegebene Ergebnis wiederverwenden wollen.
Richtig

SELECT myjson::json->'name' as CityName
FROM public.json_table
WHERE myjson::json->'weather'->0->>'main' = 'Clouds';

Verwende ->> anstelle von ->, wenn die zurückgegebenen Werte mit normalen Operatoren und Funktionen wiederverwendet werden sollen!

Ein anderes, etwas komplexeres WHERE

SELECT myjson::json->'name' as CityName, myjson::json->'main'->>'temp' as Temperature
FROM public.json_table
WHERE myjson::json->'weather'->0->>'main' = 'Clouds' AND CAST(myjson::json->'main'->>'temp' as float) > 5;

Unser zweites Beispiel mit WHERE-Klauseln bezieht sich auf alle Städte, in denen es bewölkt, aber über 5 °C ist. Da der obige API-Link den Tei '&units=metric' enthielt, werden die Temperaturen in °C angegeben. Hier ist das Ergebnis der Abfrage:

Ein letztes Beispiel zeigt die Verwendung unserer JSON-Operatoren innerhalb einer GROUP BY-Klausel.

GROUP BY mit JSON-Operatoren

GROUP BY benötigt den Operator ->> oder den Operator #>> an der letzten Stelle der Kettenreihenfolge, um gruppierbar zu sein. Das folgende Beispiel gruppiert nach Art des Wetters und gibt die Anzahl der Städte zurück.

SELECT myjson::json->'weather'->0->>'main' as WeatherType, COUNT(*) as AffectedCities
FROM public.json_table
GROUP BY myjson::json->'weather'->0->>'main'

Das Gegenstück mit #>> ist

SELECT myjson::json#>>'{weather,0,main}' as WeatherType, COUNT(*) as AffectedCities
FROM public.json_table
GROUP BY myjson::json#>>'{weather,0,main}'

Alle guten Dinge gehen irgendwann zu Ende, so auch unsere kleine JSON-Erkundungstour.  Wir haben dir gezeigt, wie man mit den Basisoperatoren durch JSON-Objekte navigieren kann, um bestimmte Elemente auszuwählen oder zu referenzieren. PostgreSQL bietet auch eine ganze Reihe von Funktionen, um die Arbeit  mit JSON-Objekten noch vielfältiger zu machen. Wir haben uns allerdings „nur” auf die häufigsten Anwendungsszenarien konzentriert. Für diese lagen die Daten bereits vor und die Beispiele haben die Unterschiede und Verwendungen der Operatoren verdeutlicht. Jetzt solltest du bestens für PostgreSQL gerüstet sein!