postgresql
Поддержка JSON

Вступление

Создание чистой таблицы JSON

Чтобы создать чистую таблицу JSON, вам нужно предоставить одно поле с типом JSONB :

CREATE TABLE mytable (data JSONB NOT NULL);

Вы также должны создать базовый индекс:

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

На этом этапе вы можете вставлять данные в таблицу и запрашивать ее эффективно.

Запрос сложных документов JSON

Принимая сложный документ JSON в таблице:

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

Запрос элемента верхнего уровня:

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

Запрос простого элемента в массиве:

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

Запрос объекта в массиве:

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

Запрос для вложенного объекта:

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

Производительность @> по сравнению с -> и ->>

Важно понимать разницу в производительности между использованием @> , -> и ->> в части WHERE запроса. Хотя эти два запроса в целом эквивалентны:

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

первый оператор будет использовать созданный выше индекс, тогда как последние два не будут, требующие полного сканирования таблицы.

По-прежнему можно использовать оператор- -> при получении результирующих данных, поэтому следующие запросы также будут использовать индекс:

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

Использование операторов JSONb

Создание базы данных и таблицы

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

Заполнение БД

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

Давайте посмотрим все в книгах:

SELECT * FROM books;

Выход:

-> возвращает значения из столбцов JSON

Выбор 1 столбца:

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

Выход:

Выбор 2 столбцов:

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

Выход:

-> vs ->>

Оператор -> возвращает исходный тип JSON (который может быть объектом), тогда как ->> возвращает текст.

Вернуть объекты NESTED

Вы можете использовать -> для возврата вложенного объекта и, таким образом, привязать операторов:

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

Выход:

фильтрация

Выберите строки, основанные на значении внутри вашего JSON:

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

Обратите внимание, что WHERE использует -> так что мы должны сравнить с JSON '"Dharma Bums"'

Или мы могли бы использовать ->> и сравнить с 'Dharma Bums'

Выход:

Вложенная фильтрация

Найти строки на основе значения вложенного объекта JSON:

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

Выход:

Пример реального мира

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

Мы собираемся хранить события в этой таблице, такие как просмотры страниц. Каждое событие имеет свойства, которые могут быть любыми (например, текущая страница), а также отправляет информацию о браузере (например, ОС, разрешение экрана и т. Д.). Оба они полностью бесплатны и могут меняться со временем (поскольку мы думаем о дополнительных материалах для отслеживания).

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

Теперь давайте выберем все:

SELECT * FROM events;

Выход:

Операторы JSON + агрегированные функции PostgreSQL

Используя JSON-операторы в сочетании с традиционными функциями PostgreSQL, мы можем вытащить все, что захотим. У вас есть полная мощь РСУБД в вашем распоряжении.

• Давайте посмотрим на использование браузера:

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

Выход:

• Общий доход на одного посетителя:

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

Выход:

• Среднее разрешение экрана

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

Выход:

Дополнительные примеры и документация здесь и здесь .