postgresql
JSON Ondersteuning

Invoering

Een pure JSON-tabel maken

Om een pure JSON-tabel te maken, moet u een enkel veld met het type JSONB :

CREATE TABLE mytable (data JSONB NOT NULL);

U moet ook een basisindex maken:

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

Op dit punt kunt u gegevens in de tabel invoegen en deze efficiënt opvragen.

Complexe JSON-documenten opvragen

Een complex JSON-document in een tabel opnemen:

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"
        }
    }
}
$$);

Zoekopdracht voor een element op het hoogste niveau:

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

Zoekopdracht voor een eenvoudig item in een array:

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

Zoekopdracht voor een object in een array:

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

Zoekopdracht voor een genest object:

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

Prestaties van @> vergeleken met -> en ->>

Het is belangrijk om het prestatieverschil te begrijpen tussen het gebruik van @> , -> en ->> in het WHERE gedeelte van de query. Hoewel deze twee vragen in grote lijnen equivalent zijn:

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

de eerste instructie gebruikt de hierboven gemaakte index, terwijl de laatste twee dat niet zullen doen, wat een volledige tabelscan vereist.

Het is nog steeds toegestaan om de -> operator te gebruiken bij het verkrijgen van resulterende gegevens, dus de volgende vragen zullen ook de index gebruiken:

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

JSONb-operatoren gebruiken

Een DB en een tabel maken

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
);

De DB vullen

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" } }'
);

Laten we alles in de tafelboeken bekijken:

SELECT * FROM books;

Output:

-> operator retourneert waarden uit JSON-kolommen

1 kolom selecteren:

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

Output:

2 kolommen selecteren:

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

Output:

-> vs ->>

De operator -> retourneert het oorspronkelijke JSON-type (dit kan een object zijn), terwijl ->> tekst retourneert.

Retourneer NESTED-objecten

U kunt de -> om een genest object terug te geven en zo de operatoren te ketenen:

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

Output:

filtering

Selecteer rijen op basis van een waarde in uw JSON:

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

Let op WHERE gebruikt -> dus we moeten vergelijken met JSON '"Dharma Bums"'

Of we kunnen ->> en vergelijken met 'Dharma Bums'

Output:

Geneste filtering

Zoek rijen op basis van de waarde van een genest JSON-object:

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

Output:

Een echt voorbeeld

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

We gaan evenementen in deze tabel opslaan, zoals paginaweergaven. Elke gebeurtenis heeft eigenschappen, die van alles kunnen zijn (bijv. Huidige pagina) en verzendt ook informatie over de browser (zoals besturingssysteem, schermresolutie, enz.). Beide zijn volledig gratis van vorm en kunnen in de loop van de tijd veranderen (omdat we denken aan extra dingen om bij te houden).

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 } }'
);

Laten we nu alles selecteren:

SELECT * FROM events;

Output:

JSON-operators + PostgreSQL-aggregatiefuncties

Met behulp van de JSON-operators, gecombineerd met traditionele PostgreSQL-aggregatiefuncties, kunnen we eruit halen wat we willen. U hebt de volledige macht van een RDBMS tot uw beschikking.

• Laten we het gebruik van de browser bekijken:

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

Output:

• Totale omzet per bezoeker:

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

Output:

• Gemiddelde schermresolutie

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

Output:

Meer voorbeelden en documentatie hier en hier .