postgresql
JSON Support

Introduktion

Skapa ett rent JSON-bord

För att skapa en ren JSON-tabell måste du ange ett enda fält med typen JSONB :

CREATE TABLE mytable (data JSONB NOT NULL);

Du bör också skapa ett grundläggande index:

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

Vid denna punkt kan du infoga data i tabellen och fråga dem effektivt.

Fråga efter komplexa JSON-dokument

Ta ett komplext JSON-dokument i en tabell:

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

Fråga för ett toppnivåelement:

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

Fråga efter ett enkelt objekt i en matris:

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

Fråga efter ett objekt i en matris:

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

Fråga för ett kapselt objekt:

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

Prestanda för @> jämfört med -> och ->>

Det är viktigt att förstå resultatskillnaden mellan att använda @> , -> och ->> i WHERE delen av frågan. Även om dessa två frågor verkar vara i stort sett likvärdiga:

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

det första uttalandet kommer att använda indexet som skapats ovan medan de två senare inte kommer att kräva en fullständig tabellscanning.

Det är fortfarande tillåtet att använda operatören -> när man skaffar resulterande data, så följande frågor kommer också att använda indexet:

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

Använda JSONb-operatörer

Skapa en DB och en tabell

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

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

Låter se allt inne i bordsböckerna:

SELECT * FROM books;

Produktion:

-> operatören returnerar värden från JSON-kolumnerna

Att välja 1 kolumn:

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

Produktion:

Att välja två kolumner:

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

Produktion:

-> vs ->>

Operatören -> returnerar den ursprungliga JSON-typen (som kan vara ett objekt), medan ->> returnerar text.

Returnera NESTED-objekt

Du kan använda -> att returnera ett kapselt objekt och därmed kedja operatörerna:

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

Produktion:

Filtrering

Välj rader baserat på ett värde i din JSON:

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

Lägg märke till VAR använder -> så vi måste jämföra med JSON '"Dharma Bums"'

Eller så kan vi använda ->> och jämföra med 'Dharma Bums'

Produktion:

Kapslad filtrering

Hitta rader baserade på värdet på ett kapslat JSON-objekt:

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

Produktion:

Ett verkligt exempel

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

Vi kommer att lagra händelser i den här tabellen, som sidvisningar. Varje evenemang har egenskaper som kan vara vad som helst (t.ex. aktuell sida) och skickar också information om webbläsaren (som OS, skärmupplösning osv.). Båda dessa är helt fria och kan förändras över tid (eftersom vi tänker på extra saker att spåra).

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

Nu kan vi välja allt:

SELECT * FROM events;

Produktion:

JSON-operatörer + PostgreSQL aggregerade funktioner

Med hjälp av JSON-operatörerna, i kombination med traditionella PostgreSQL-aggregeringsfunktioner, kan vi dra ut vad vi vill. Du har en fullständig styrka av ett RDBMS till ditt förfogande.

• Låter se webbläsaranvändning:

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

Produktion:

• Total intäkt per besökare:

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

Produktion:

• Genomsnittlig skärmupplösning

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

Produktion:

Fler exempel och dokumentation här och här .