После комитфеста 2019-03 произошла заморозка функциональности (feature freeze). У нас это почти традиционная рубрика: о прошлогодней заморозке мы уже писали. Теперь итоги 2019: что из нового войдет в PostgreSQL 12. В этой части обзора, посвященной JSONPath, используются в том числе примеры и фрагменты из доклада «Postgres 12 в этюдах», который прочитал Олег Бартунов на Saint Highload++ в СПБ 9 апреля сего года.
JSONPath
Всё, что связано с JSON(B), актуально, востребовано в мире, в России, и это одно из важнейших направлений разработки в компании Postgres Professional. Тип jsonb, функции и операторы для работы с JSON/JSONB появились еще в версии PostgreSQL 9.4, их делала команда под руководством Олега Бартунова.
Стандартом SQL/2016 предусмотрена работа с JSON: там упоминается JSONPath — набор средств адресации данных внутри JSON; JSONTABLE — средства конвертации JSON в обычные таблицы; большое семейство функций и операторов. Несмотря на то, что JSON в Postgres поддерживается давно, в 2017 г Олег Бартунов с коллегами начали работать над поддержкой стандарта. Соответствовать стандарту — это всегда хорошо. Из всего, что описано в стандарте, в версию 12 попал только один, но важнейший патч — JSONPath, поэтому расскажем о нём в первую очередь.
В стародавние времена люди пользовались JSON, храня его в текстовых полях. В 9.3 появился специальный тип данных для JSON-а, но функциональность, связанная с ним, была небогата, и запросы с этим типом работали медленно из-за затрат времени на парсинг текстового представления JSON. Это останавливало многих потенциальных пользователей Postgres, предпочитавших NoSQL-базы. Производительность Postgres повысилась в 9.4, когда, благодаря О.Бартунову, А.Короткову и Ф.Сигаеву, в Postgres появился бинарный вариант JSON — тип jsonb.
jsonb не нужно каждый раз парсить, поэтому работать с ним гораздо быстрее. Из возникших одновременно с ним новых функций и операторов некоторые работают только с новым, бинарным типом, как, например, важный оператор вхождения @>, который проверяет, входит ли элемент или массив в данный JSONB:
SELECT '[1, 2, 3]'::jsonb @> '[1, 3]'::jsonb;даёт TRUE, так как массив с правой стороны входит в массив слева. Но
SELECT '[1, 2, [1, 3]]'::jsonb @> '[1, 3]'::jsonb;даст FALSE, так как в уровень вложенности другой, его надо задавать явно. Для типа jsonb введен оператор существования ? (вопросительный знак), проверяющий, является ли строка ключом объекта или элементом массива на верхнем уровне значения JSONB, а также еще два похожих оператора (подробности здесь). Они поддерживаются индексами GIN с двумя классами операторов GIN. Оператор -> (стрелка) позволяет «перемещаться» по JSONB, он выдает значение по ключу или, если это массив, по индексу. Есть еще несколько операторов для перемещений. Но нет возможности организовать фильтры, работающие аналогично WHERE. Это был прорыв: благодаря jsonb, начался рост популярности Postgres как РСУБД с NoSQL фичами.
В 2014 году А.Коротковым, О.Бартуновым и Ф.Сигаевым было разработано расширение jsquery, вошедшее в результате в версию Postgres Pro Standard 9.5 (и в более поздние версии Standard и Enterprise). Оно дает дополнительные, очень широкие возможности для работы с json(b). Это расширение определяет язык запросов для извлечения данных из json(b) и индексы для ускорения этих запросов. Эта функциональность требовалась пользователям, они не готовы были ждать стандарта и включения новых функций в ванильную версию. О практической ценности свидетельствует и тот факт, что разработка спонсировалась Wargaming.net. В расширении реализован специальный тип — jsquery.
Запрос на этом языке компактен и выглядит, например, так:
SELECT '{"apt":[{"no": 1, "rooms":2}, {"no": 2, "rooms":3}, {"no": 3, "rooms":2}]}'::jsonb @@ 'apt.#.rooms=3'::jsquery;Мы здесь спрашиваем, есть ли в доме квартиры «трёшки». Тип jsquery приходится указывать потому, что оператор @@ есть теперь и в типе jsonb. Описание здесь, а презентация со многими примерами здесь.
Итого: В Postgres для работы с JSON всё уже было, и тут появился стандарт SQL:2016. Оказалось, что его семантика не так уж сильно отличается нашей в расширении jsquery. Не исключено, что авторы стандарта даже поглядывали на jsquery, изобретая JSONPath. Нашей команде пришлось реализовывать немного по-другому то, что у нас уже было и, конечно, много нового тоже.
Больше года назад, на мартовском коммитфесте плоды наших программистских усилий были предложены сообществу в виде 3 больших патчей с поддержкой стандарта SQL:2016:
SQL/JSON: JSONPath;
SQL/JSON: functions;
SQL/JSON: JSON_TABLE.
Но разработать патч еще не всё дело, продвигать их тоже непросто, особенно если патчи большие и затрагивает много модулей. Требуется много итераций ревю-доработки, патч надо продвигать, как это делают коммерческие компании, вкладывая немало ресурсов (человеко-часов). Главный архитектор Postgres Professional, Александр Коротков, взялся за это сам (благо у него теперь статус коммиттера) и добился принятия патча JSONPath — главного из этой серии патчей. Второй и третий сейчас в статусе Needs Review. JSONPath, на котором сосредоточили усилия, позволяет работать со структурой JSON(B) и он достаточно гибок, чтобы выделять его фрагменты. Из 15 пунктов, прописанных в стандарте, реализованы 14, а это больше, чем в Oracle, MySQL и MS SQL.
Нотация в JSONPath отличается от операторов Postgres для работы с JSON и от нотации JSQuery. Иерархию обозначают точками:
$.a.b.c (в нотации postgres 11 пришлось бы написать 'a'->'b'->'c');
$ — текущий контекст элемента — фактически выражение с $ задает область json(b), которая подлежит обработке, в том числе фигурирует в фильтре, остальная часть в этом случае для работы недоступна;
@ — текущий контекст в выражении-фильтре — перебираются пути, доступные в выражении с $;
[*] — массив;
* — wildcard, в выражении с $ или @ означает любое значение участка пути, но с учетом иерархии;
** — как часть выражения с $ или @ может означать любое значение участка пути без учета иерархии — удобно использовать, если не знаем уровень вложенности элементов;
оператор "?" позволяет организовать фильтр, аналогичный WHERE:
$.a.b.c? (@.x > 10);
$.a.b.c.x.type(), а также size(), double(), ceiling(), floor(), abs(), datetime(), keyvalue() — методы.
Запрос с функцией jsonb_path_query (о функциях ниже) может выглядеть так:
SELECT jsonb_path_query_array('[1,2,3,4,5]', '$[*] ? (@ > 3)');
jsonb_path_query_array
------------------------
[4, 5]
(1 row)Хотя специальный патч с функциями не закоммичен, в патче JSONPath уже есть ключевые функции для работы с JSON(B):
jsonb_path_exists('{"a": 1}', '$.a') возвращает true (вызывается оператором "?")
jsonb_path_exists('{"a": 1}', '$.b') возвращает false
jsonb_path_match('{"a": 1}', '$.a == 1') возвращает true (вызывается оператором "@>")
jsonb_path_match('{"a": 1}', '$.a >= 2') возвращает false
jsonb_path_query('{"a": [1,2,3,4,5]}', '$.a[*] ? (@ > 2)') возвращает 3, 4, 5
jsonb_path_query('{"a": [1,2,3,4,5]}', '$.a[*] ? (@ > 5)') возвращает 0 записей
jsonb_path_query_array('{"a": [1,2,3,4,5]}', '$.a[*] ? (@ > 2)') возвращает [3, 4, 5]
jsonb_path_query_array('{"a": [1,2,3,4,5]}', '$.a[*] ? (@ > 5)') возвращает []
jsonb_path_query_first('{"a": [1,2,3,4,5]}', '$.a[*] ? (@ > 2)') возвращает 3
jsonb_path_query_first('{"a": [1,2,3,4,5]}', '$.a[*] ? (@ > 5)') возвращает NULLОбратите внимание, что равенство в выражениях JSONPath это одиночное "=", а в jsquery двойное: "==".
Для более изящных иллюстраций сгенерим JSONB в одноколоночной табличке house:
CREATE TABLE house(js jsonb);
INSERT INTO house VALUES
('{
"address": {
"city":"Moscow",
"street": "Ulyanova, 7A"
},
"lift": false,
"floor": [
{
"level": 1,
"apt": [
{"no": 1, "area": 40, "rooms": 1},
{"no": 2, "area": 80, "rooms": 3},
{"no": 3, "area": 50, "rooms": 2}
]
},
{
"level": 2,
"apt": [
{"no": 4, "area": 100, "rooms": 3},
{"no": 5, "area": 60, "rooms": 2}
]
}
]
}');
Рис.1 Дерево Жилищного JSON-а с выделенными квартирами-листьями.
Это странный JSON: у него спутанная иерархия, но он взят из жизни, а в жизни нередко приходится работать с тем, что есть, а не с тем, что должно быть. Вооружившись возможностями новой версии, найдем квартиры на 1-м и 2-м этажах, но не первые в списке квартир этажа (на дереве они выделены зеленым):
SELECT jsonb_path_query_array(js, '$.floor[0, 1].apt[1 to last]')
FROM house;
---------------------
[{"no": 2, "area": 80, "rooms": 3}, {"no": 3, "area": 50, "rooms": 2}, {"no": 5, "area": 60, "rooms": 2}]
В PostgreSQL 11 придется спросить так:
SELECT jsonb_agg(apt) FROM (
SELECT apt->generate_series(1, jsonb_array_length(apt) - 1) FROM (
SELECT js->'floor'->unnest(array[0, 1])->'apt' FROM house
) apts(apt)
) apts(apt);Теперь совсем простой вопрос: есть ли строчки, содержащие (в любом месте) значение «Moscow»? Действительно простой:
SELECT jsonb_path_exists(js, '$.** ? (@ == "Moscow")') FROM house;В версии 11 пришлось бы написать огромный скрипт:
WITH RECURSIVE t(value) AS (
SELECT * FROM house UNION ALL (
SELECT COALESCE(kv.value, e.value) AS value
FROM t
LEFT JOIN LATERAL jsonb_each (
CASE WHEN jsonb_typeof(t.value) = 'object' THEN t.value
ELSE NULL END
) kv ON true
LEFT JOIN LATERAL jsonb_array_elements (
CASE WHEN jsonb_typeof(t.value) = 'array' THEN t.value
ELSE NULL END
) e ON true
WHERE kv.value IS NOT NULL OR e.value IS NOT NULL
)
) SELECT EXISTS (SELECT 1 FROM t WHERE value = '"Moscow"');
Рис.2 Дерево Жилищного JSON-а, найдена Москва!
Ищем любую квартиру на любом этаже площадью от 40 до 90 кв.м:
select jsonb_path_query(js, '$.floor[*].apt[*] ? (@.area > 40 && @.area < 90)') FROM house;
jsonb_path_query
-----------------------------------
{"no": 2, "area": 80, "rooms": 3}
{"no": 3, "area": 50, "rooms": 2}
{"no": 5, "area": 60, "rooms": 2}
(3 rows)
Ищем квартиры с номерами после 3-й, используя наш жилищный джейсон:
SELECT jsonb_path_query(js, '$.floor.apt.no ? (@>3)') FROM house;
jsonb_path_query
------------------
4
5
(2 rows)А вот как работает jsonb_path_query_first:
SELECT jsonb_path_query_first(js, '$.floor.apt.no ? (@>3)') FROM house;
jsonb_path_query_first
------------------------
4
(1 row)Видим, что выбрано только первое значение, удовлетворившее условию фильтра.
Булев оператор JSONPath для JSONB @@ называется оператором соответствия. Он вычисляет предикат JSONPath, вызывая функцию jsonb_path_match_opr.
Другой булев оператор — @? — это проверка существования, отвечает на вопрос возвратит ли выражение JSONPath объекты SQL/JSON, вызывает функцию jsonb_path_exists_opr:
проверка '[1,2,3]' @@ '$[*] == 3' возвращает true;
и '[1,2,3]' @? '$[*] @? (@ == 3)' - тоже trueОдного и того же результата можно достичь, используя разные операторы:
js @? '$.a' эквивалентно js @@ 'exists($.a)'
js @@ '$.a == 1' эквивалентно js @? '$ ? ($.a == 1)'Прелесть булевых операторов JSONPath в том, что они поддерживаются, ускоряются индексами GIN. jsonb_ops и jsonb_path_ops — соответствующие классы операторов. В примере отключаем SEQSCAN, так как у нас микротаблица, на больших таблицах оптимизатор сам выберет Bitmap Index:
SET ENABLE_SEQSCAN TO OFF;
CREATE INDEX ON house USING gin (js);
EXPLAIN (COSTS OFF) SELECT * FROM house
WHERE js @? '$.floor[*].apt[*] ? (@.rooms == 3)';
QUERY PLAN
--------------------------------------------------------------------------------
Bitmap Heap Scan on house
Recheck Cond: (js @? '$."floor"[*]."apt"[*]?(@."rooms" == 3)'::jsonpath)
-> Bitmap Index Scan on house_js_idx
Index Cond: (js @? '$."floor"[*]."apt"[*]?(@."rooms" == 3)'::jsonpath)
(4 rows)У всех функций вида jsonb_path_xxx() одинаковая сигнатура:
jsonb_path_xxx(
js jsonb,
jsp jsonpath,
vars jsonb DEFAULT '{}',
silent boolean DEFAULT false
)vars — это объект JSONB для передачи переменных JSONPath:
SELECT jsonb_path_query_array('[1,2,3,4,5]', '$[*] ? (@ > $x)',
vars => '{"x": 2}');
jsonb_path_query_array
------------------------
[3, 4, 5]Без vars трудно обойтись, когда мы делаем джойн с участием поля типа jsonb в одной из таблиц. Скажем, мы делаем приложение, которое ищет в том самом доме подходящие квартиры для сотрудников, которые записали в анкете свои требования к минимальной площади:
CREATE TABLE demands(name text, position text, demand int);
INSERT INTO demands VALUES ('Саша','босс', 85), ('Паша','младший программист', 45);
SELECT jsonb_path_query(js, '$.floor[*].apt[*] ? (@.area >= $min)', vars => jsonb_build_object('min', demands.demand)) FROM house, demands WHERE name = 'Паша';
-[ RECORD 1 ]----+-----------------------------------
jsonb_path_query | {"no": 2, "area": 80, "rooms": 3}
-[ RECORD 2 ]----+-----------------------------------
jsonb_path_query | {"no": 3, "area": 50, "rooms": 2}
-[ RECORD 3 ]----+-----------------------------------
jsonb_path_query | {"no": 4, "area": 100, "rooms": 3}
-[ RECORD 4 ]----+-----------------------------------
jsonb_path_query | {"no": 5, "area": 60, "rooms": 2}Счастливец Паша может выбирать из 4 квартир. Но стоит изменить 1 букву в запросе — с «П» на «С», и выбора не останется! Подойдет только 1 квартира.
Остается еще одно ключевое слово: silent — это флаг, подавляющий обработку ошибок, они на совести программиста.
SELECT jsonb_path_query('[]', 'strict $.a');
ERROR: SQL/JSON member not found
DETAIL: jsonpath member accessor can only be applied to an objectОшибка. Но вот так ошибки не будет:
SELECT jsonb_path_query('[]', 'strict $.a', silent => true);
jsonb_path_query
------------------
(0 rows)Кстати, об ошибках: в соответствии со стандартом, арифметические ошибки в выражениях не выдают сообщения об ошибке, они на совести программиста:
SELECT jsonb_path_query('[1,0,2]', '$[*] ? (1/ @ >= 1)');
jsonb_path_query
------------------
1
(1 row)При вычислении выражения в фильтре перебираются значения массива, среди которых есть и 0, но деление на 0 не генерирует ошибку.
Функции будут работать по-разному в зависимости от выбранного режима: Strict или Lax (в переводе «нестрогий», а то и «расхлябанный», он выбирается по умолчанию). Допустим, мы ищем ключ в режиме Lax в JSON-е, где его заведомо нет:
SELECT jsonb '{"a":1}' @? 'lax $.b ? (@ > 1)';
?column?
----------
f
(1 row)Теперь в режиме Strict:
SELECT jsonb '{"a":1}' @? 'strict $.b ? (@ > 1)';
?column?
----------
(null)
(1 row)То есть там, где при либеральном режиме мы получали FALSE, при строгом мы получили NULL.
В режиме Lax массив со сложной иерархией [1,2,[3,4,5]] всегда разворачивается в [1,2,3,4,5]:
SELECT jsonb '[1,2,[3,4,5]]' @? 'lax $[*] ? (@ == 5)';
?column?
----------
t
(1 row)В режиме Strict число «5» не будет найдено, так как оно не на нижней ступеньке иерархии. Чтобы его найти, придетеся видоизменить запрос, заменив "@" на "@[*]":
SELECT jsonb '[1,2,[3,4,5]]' @? 'strict $[*] ? (@[*] == 5)';
?column?
----------
t
(1 row)В PostgreSQL 12 JSONPath это тип данных. Стандарт ничего не говорит о необходимости нового типа, это свойство имплементации. С новым типом мы получаем полноценную работу с jsonpath при помощи операторов и ускоряющих их работу индексов, уже существующих для JSONB. Иначе пришлось бы интегрировать JSONPath на уровне кода исполнителя и оптимизатора.
О синтаксисе SQL/JSON можно почитать, например, здесь.
В блоге Олега Бартунова есть о SQL/JSON standard-2016 conformance for PostgreSQL, Oracle, SQL Server and MySQL.
Здесь презентация по SQL/JSON.
А здесь — введение в SQL/JSON.