postgresql
JSON-Unterstützung

Einführung

Eine reine JSON-Tabelle erstellen

Um eine reine JSON-Tabelle zu erstellen, müssen Sie ein einzelnes Feld mit dem Typ JSONB :

CREATE TABLE mytable (data JSONB NOT NULL);

Sie sollten auch einen Basisindex erstellen:

CREATE INDEX mytable_idx ON mytable USING gin (data jsonb_path_ops);

An dieser Stelle können Sie Daten in die Tabelle einfügen und diese effizient abfragen.

Abfragen komplexer JSON-Dokumente

Ein komplexes JSON-Dokument in eine Tabelle aufnehmen:

CREATE TABLE mytable (data JSONB NOT NULL);
CREATE INDEX mytable_idx ON mytable USING gin (data jsonb_path_ops);
INSERT INTO mytable VALUES($$
{
    "name": "Alice",
    "emails": [
        "[email protected]",
        "[email protected]"
    ],
    "events": [
        {
            "type": "birthday",
            "date": "1970-01-01"
        },
        {
            "type": "anniversary",
            "date": "2001-05-05"
        }
    ],
    "locations": {
        "home": {
            "city": "London",
            "country": "United Kingdom"
        },
        "work": {
            "city": "Edinburgh",
            "country": "United Kingdom"
        }
    }
}
$$);

Abfrage für ein Element der obersten Ebene:

SELECT data->>'name' FROM mytable WHERE data @> '{"name":"Alice"}';

Abfrage nach einem einfachen Element in einem Array:

SELECT data->>'name' FROM mytable WHERE data @> '{"emails":["[email protected]"]}';

Abfrage für ein Objekt in einem Array:

SELECT data->>'name' FROM mytable WHERE data @> '{"events":[{"type":"anniversary"}]}';

Abfrage für ein verschachteltes Objekt:

SELECT data->>'name' FROM mytable WHERE data @> '{"locations":{"home":{"city":"London"}}}';

Leistung von @> Vergleich zu -> und ->>

Es ist wichtig, die Leistungsunterschiede zwischen @> , -> und ->> im WHERE Teil der Abfrage zu verstehen. Obwohl diese beiden Abfragen weitgehend gleichwertig erscheinen:

SELECT data FROM mytable WHERE data @> '{"name":"Alice"}';
SELECT data FROM mytable WHERE data->'name' = '"Alice"';
SELECT data FROM mytable WHERE data->>'name' = 'Alice';

Die erste Anweisung verwendet den oben erstellten Index, während die letzten beiden nicht erforderlich sind und einen vollständigen Tabellenscan erfordern.

Es ist weiterhin zulässig, den Operator -> , wenn die resultierenden Daten abgerufen werden. Daher verwenden die folgenden Abfragen auch den Index:

SELECT data->'locations'->'work' FROM mytable WHERE data @> '{"name":"Alice"}';
SELECT data->'locations'->'work'->>'city' FROM mytable WHERE data @> '{"name":"Alice"}';

JSONb-Operatoren verwenden

Anlegen einer Datenbank und einer Tabelle

DROP DATABASE IF EXISTS books_db;
CREATE DATABASE books_db WITH ENCODING='UTF8' TEMPLATE template0;

DROP TABLE IF EXISTS books;

CREATE TABLE books (
  id SERIAL PRIMARY KEY,
  client TEXT NOT NULL,
  data JSONb NOT NULL
);

DB füllen

INSERT INTO books(client, data) values (
    'Joe', 
    '{ "title": "Siddhartha", "author": { "first_name": "Herman", "last_name": "Hesse" } }'
),(
    'Jenny', 
    '{ "title": "Dharma Bums", "author": { "first_name": "Jack", "last_name": "Kerouac" } }'
),(
    'Jenny', 
    '{ "title": "100 años de soledad", "author": { "first_name": "Gabo", "last_name": "Marquéz" } }'
);

Lasst uns alles in den Tabellenbüchern sehen:

SELECT * FROM books;

Ausgabe:

-> Operator gibt Werte aus JSON-Spalten zurück

1 Spalte auswählen:

SELECT client, 
    data->'title' AS title
    FROM books;

Ausgabe:

Auswahl von 2 Spalten:

SELECT client, 
   data->'title' AS title, data->'author' AS author
   FROM books;

Ausgabe:

-> vs ->>

Der Operator -> gibt den ursprünglichen JSON-Typ (möglicherweise ein Objekt) zurück, während ->> Text zurückgibt.

NESTED-Objekte zurückgeben

Sie können mit -> ein verschachteltes Objekt zurückgeben und somit die Operatoren verketten:

SELECT client, 
   data->'author'->'last_name' AS author
   FROM books;

Ausgabe:

Filterung

Wählen Sie Zeilen basierend auf einem Wert in Ihrem JSON aus:

 SELECT 
 client,
 data->'title' AS title
 FROM books
  WHERE data->'title' = '"Dharma Bums"';

Beachten Sie, WO verwendet -> also müssen wir mit '"Dharma Bums"' von JSON vergleichen

Oder wir könnten ->> und mit 'Dharma Bums'

Ausgabe:

Verschachtelte Filterung

Suchen Sie Zeilen basierend auf dem Wert eines geschachtelten JSON-Objekts:

SELECT 
 client,
 data->'title' AS title
 FROM books
  WHERE data->'author'->>'last_name' = 'Kerouac';

Ausgabe:

Ein reales Beispiel

CREATE TABLE events (
  name varchar(200),
  visitor_id varchar(200),
  properties json,
  browser json
);

In dieser Tabelle werden Ereignisse wie Seitenaufrufe gespeichert. Jedes Ereignis hat Eigenschaften, die alles sein können (zB aktuelle Seite) und auch Informationen über den Browser (wie Betriebssystem, Bildschirmauflösung usw.) senden. Beide sind vollkommen frei und können sich im Laufe der Zeit ändern (wenn wir an zusätzliche Dinge denken, um sie zu verfolgen).

INSERT INTO events (name, visitor_id, properties, browser) VALUES
(
  'pageview', '1',
  '{ "page": "/" }',
  '{ "name": "Chrome", "os": "Mac", "resolution": { "x": 1440, "y": 900 } }'
),(
  'pageview', '2',
  '{ "page": "/" }',
  '{ "name": "Firefox", "os": "Windows", "resolution": { "x": 1920, "y": 1200 } }'
),(
  'pageview', '1',
  '{ "page": "/account" }',
  '{ "name": "Chrome", "os": "Mac", "resolution": { "x": 1440, "y": 900 } }'
),(
  'purchase', '5',
  '{ "amount": 10 }',
  '{ "name": "Firefox", "os": "Windows", "resolution": { "x": 1024, "y": 768 } }'
),(
  'purchase', '15',
  '{ "amount": 200 }',
  '{ "name": "Firefox", "os": "Windows", "resolution": { "x": 1280, "y": 800 } }'
),(
  'purchase', '15',
  '{ "amount": 500 }',
  '{ "name": "Firefox", "os": "Windows", "resolution": { "x": 1280, "y": 800 } }'
);

Jetzt können wir alles auswählen:

SELECT * FROM events;

Ausgabe:

JSON-Operatoren + Aggregatefunktionen von PostgreSQL

Mit den JSON-Operatoren können wir in Kombination mit den herkömmlichen PostgreSQL-Aggregatfunktionen das ausführen, was wir wollen. Sie haben alle Möglichkeiten eines RDBMS zu Ihrer Verfügung.

• Sehen Sie sich die Browserverwendung an:

    SELECT browser->>'name' AS browser, 
      count(browser)
      FROM events
      GROUP BY browser->>'name';
  

Ausgabe:

• Gesamtumsatz pro Besucher:

    SELECT visitor_id, SUM(CAST(properties->>'amount' AS integer)) AS total
    FROM events
    WHERE CAST(properties->>'amount' AS integer) > 0
    GROUP BY visitor_id;
  

Ausgabe:

• Durchschnittliche Bildschirmauflösung

    SELECT AVG(CAST(browser->'resolution'->>'x' AS integer)) AS width,
      AVG(CAST(browser->'resolution'->>'y' AS integer)) AS height
    FROM events;
  

Ausgabe:

Weitere Beispiele und Dokumentation hier und hier .