postgresql
Supporto JSON

introduzione

Creazione di una tabella JSON pura

Per creare una tabella JSON pura è necessario fornire un campo singolo con il tipo JSONB :

CREATE TABLE mytable (data JSONB NOT NULL);

Dovresti anche creare un indice di base:

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

A questo punto è possibile inserire i dati nella tabella e interrogarli in modo efficiente.

Interrogazione di documenti JSON complessi

Prendere un documento JSON complesso in una tabella:

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

Query per un elemento di primo livello:

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

Query per un elemento semplice in un array:

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

Query per un oggetto in una matrice:

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

Query per un oggetto nidificato:

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

Prestazioni di @> rispetto a -> e ->>

È importante capire la differenza di prestazioni tra l'utilizzo di @> , -> e ->> nella parte WHERE della query. Sebbene queste due query sembrino essere sostanzialmente equivalenti:

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

la prima istruzione utilizzerà l'indice creato sopra mentre le ultime due non lo faranno, richiedendo una scansione completa della tabella.

È ancora possibile utilizzare l'operatore -> quando si ottengono i dati risultanti, quindi le seguenti query utilizzeranno anche l'indice:

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

Usando gli operatori JSONb

Creazione di un DB e una tabella

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

Popolazione del DB

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

Vediamo tutto all'interno dei libri da tavolo:

SELECT * FROM books;

Produzione:

-> operatore restituisce valori dalle colonne JSON

Selezione di 1 colonna:

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

Produzione:

Selezione di 2 colonne:

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

Produzione:

-> vs ->>

L'operatore -> restituisce il tipo JSON originale (che potrebbe essere un oggetto), mentre ->> restituisce il testo.

Restituisci oggetti NESTED

Puoi usare -> per restituire un oggetto nidificato e quindi concatenare gli operatori:

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

Produzione:

filtraggio

Seleziona le righe in base a un valore all'interno del tuo JSON:

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

Avviso WHERE utilizza -> quindi dobbiamo confrontare con '"Dharma Bums"' di JSON

O potremmo usare ->> e confrontare con 'Dharma Bums'

Produzione:

Filtraggio nidificato

Trova le righe in base al valore di un oggetto JSON nidificato:

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

Produzione:

Un esempio reale

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

Stiamo andando a memorizzare eventi in questa tabella, come pagine visualizzate. Ogni evento ha proprietà, che potrebbero essere qualsiasi cosa (ad esempio la pagina corrente) e invia anche informazioni sul browser (come OS, risoluzione dello schermo, ecc.). Entrambi sono completamente gratuiti e potrebbero cambiare nel tempo (poiché pensiamo a cose extra da tenere traccia).

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

Ora lascia selezionare tutto:

SELECT * FROM events;

Produzione:

Operatori JSON + funzioni aggregate PostgreSQL

Usando gli operatori JSON, combinati con le tradizionali funzioni di aggregazione di PostgreSQL, possiamo estrarre tutto ciò che vogliamo. Hai tutta la potenza di un RDBMS a tua disposizione.

• Vediamo l'utilizzo del browser:

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

Produzione:

• Entrate totali per visitatore:

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

Produzione:

• Media risoluzione dello schermo

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

Produzione:

Altri esempi e documentazione qui e qui .