postgresql
Wsparcie JSON

Wprowadzenie

Tworzenie czystej tabeli JSON

Aby utworzyć czystą tabelę JSON, musisz podać jedno pole typu JSONB :

CREATE TABLE mytable (data JSONB NOT NULL);

Powinieneś także utworzyć podstawowy indeks:

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

W tym momencie możesz wstawiać dane do tabeli i wydajnie wyszukiwać je.

Sprawdzanie złożonych dokumentów JSON

Biorąc złożony dokument JSON w tabeli:

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

Zapytanie o element najwyższego poziomu:

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

Zapytanie o prosty element w tablicy:

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

Zapytanie o obiekt w tablicy:

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

Zapytanie o zagnieżdżony obiekt:

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

Wydajność @> porównaniu do -> i ->>

Ważne jest, aby zrozumieć różnicę wydajności między używaniem @> , -> i ->> w WHERE części zapytania. Chociaż te dwa zapytania wydają się zasadniczo równoważne:

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

pierwsza instrukcja będzie korzystać z indeksu utworzonego powyżej, a dwie ostatnie nie, wymagając pełnego skanowania tabeli.

Nadal można używać operatora -> podczas uzyskiwania danych wynikowych, więc następujące zapytania będą również korzystać z indeksu:

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

Korzystanie z operatorów JSONb

Tworzenie bazy danych i tabeli

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

Wypełnianie bazy danych

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

Pozwala zobaczyć wszystko w książkach stołowych:

SELECT * FROM books;

Wynik:

-> operator zwraca wartości z kolumn JSON

Wybór 1 kolumny:

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

Wynik:

Wybieranie 2 kolumn:

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

Wynik:

-> vs ->>

Operator -> zwraca oryginalny typ JSON (który może być obiektem), natomiast ->> zwraca tekst.

Zwróć NESTED obiekty

Możesz użyć -> aby zwrócić zagnieżdżony obiekt, a tym samym połączyć operatory:

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

Wynik:

Filtracja

Wybierz wiersze na podstawie wartości wewnątrz JSON:

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

Zauważ, GDZIE używa -> więc musimy porównać do JSON '"Dharma Bums"'

Lub możemy użyć ->> i porównać do 'Dharma Bums'

Wynik:

Filtrowanie zagnieżdżone

Znajdź wiersze na podstawie wartości zagnieżdżonego obiektu JSON:

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

Wynik:

Przykład z prawdziwego świata

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

Będziemy przechowywać wydarzenia w tej tabeli, takie jak odsłony. Każde zdarzenie ma właściwości, które mogą być dowolne (np. Bieżąca strona), a także wysyła informacje o przeglądarce (takie jak system operacyjny, rozdzielczość ekranu itp.). Oba są całkowicie darmową formą i mogą z czasem ulec zmianie (w przypadku dodatkowych rzeczy do śledzenia).

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

Teraz wybierzmy wszystko:

SELECT * FROM events;

Wynik:

Operatory JSON + funkcje agregujące PostgreSQL

Używając operatorów JSON, w połączeniu z tradycyjnymi funkcjami agregującymi PostgreSQL, możemy wyciągnąć co tylko chcemy. Masz do dyspozycji pełną moc RDBMS.

• Pozwala zobaczyć użycie przeglądarki:

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

Wynik:

• Całkowity przychód na użytkownika:

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

Wynik:

• Średnia rozdzielczość ekranu

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

Wynik:

Więcej przykładów i dokumentacji tutaj i tutaj .