http://habrahabr.ru/post/254425/
Ранее я писал, как включить поддержку jsonb в
postgres/psycopg2. Сегодня экспериментировал с тем, как запрашивать данные в колонках типа JSON.
На эту тему есть
документация, но мне было не совсем понятно, как работают различные операции:
CREATE TABLE json_test (
id serial primary key,
data jsonb
);
INSERT INTO json_test (data) VALUES
('{}'),
('{"a": 1}'),
('{"a": 2, "b": ["c", "d"]}'),
('{"a": 1, "b": {"c": "d", "e": true}}'),
('{"b": 2}');
Запрос отработал, давайте выведем все данные, чтобы проверить:
SELECT * FROM json_test;
id | data
----+--------------------------------------
1 | {}
2 | {"a": 1}
3 | {"a": 2, "b": ["c", "d"]}
4 | {"a": 1, "b": {"c": "d", "e": true}}
5 | {"b": 2}
(5 rows)
Теперь сделаем фильтрацию результатов. Есть несколько операторов, которые мы можем использовать, и дальше увидим, почему в качестве типа выбрали jsonb.
Равенство
В jsonb мы можем проверить, что два JSON объекта идентичны:
SELECT * FROM json_test WHERE data = '{"a":1}';
id | data
----+------
1 | {"a": 1}
(1 row)
Ограничения
Также мы можем получить json объект, содержащий другой, т.е. «являющийся подмножеством»:
SELECT * FROM json_test WHERE data @> '{"a":1}';
Говорит: — Дай нам все объекты начинающиеся с ключа а и значения 1:
id | data
----+--------------------------------------
2 | {"a": 1}
4 | {"a": 1, "b": {"c": "d", "e": true}}
(2 rows)
Ограничения в обоих направлениях:
В этом случае запрос выведет пустой объект и точное совпадение для второго:
SELECT * FROM json_test WHERE data <@ '{"a":1}';
id | data
----+----------
1 | {}
2 | {"a": 1}
(2 rows)
Существование ключ/элемент
Последней партией операторов проверим существование ключа (или элемента типа строка в массиве).
id | data
----+--------------------------------------
2 | {"a": 1}
3 | {"a": 2, "b": ["c", "d"]}
4 | {"a": 1, "b": {"c": "d", "e": true}}
(3 rows)
Получим объекты, имеющие любые ключи из списка:
SELECT * FROM json_test WHERE data ?| array['a', 'b'];
id | data
----+--------------------------------------
2 | {"a": 1}
3 | {"a": 2, "b": ["c", "d"]}
4 | {"a": 1, "b": {"c": "d", "e": true}}
5 | {"b": 2}
(4 rows)
И все значения объектов имеющие точное соответствие ключей из списка:
SELECT * FROM json_test WHERE data ?& array['a', 'b'];
id | data
----+--------------------------------------
3 | {"a": 2, "b": ["c", "d"]}
4 | {"a": 1, "b": {"c": "d", "e": true}}
(2 rows)
Ключи для обхода
Вы так же можете фильтровать записи имеющие соответствие key->path. В простых случаях использование операторов ограничения может быть проще, но не в сложных, ими не обойтись. Эти операции мы можем использовать в SELECT, но все же интереснее применить их в выражении WHERE.
SELECT * FROM json_test WHERE data ->> 'a' > '1';
Получаем все записи значений ассоциативного элемента с ключом a равным 1.
Обратите внимание на необходимость использования текстового значения, а не числа:
id | data
----+---------------------------
3 | {"a": 2, "b": ["c", "d"]}
(1 row)
Можем сделать сравнение между примитивами объектов и массивов:
SELECT * FROM json_test WHERE data -> 'b' > '1';
id | data
----+--------------------------------------
3 | {"a": 2, "b": ["c", "d"]}
4 | {"a": 1, "b": {"c": "d", "e": true}}
5 | {"b": 2}
(3 rows)
Получается, что массивы и объекты больше чем цифры.
Так же можем посмотреть более глубокий path:
SELECT * FROM json_test WHERE data #> '{b,c}' = '"d"';
Получим объект, где элемент b имеет дочерний объект c, и с равен строке «d»:
id | data
----+--------------------------------------
4 | {"a": 1, "b": {"c": "d", "e": true}}
Так же есть версии этих операторов, которые возвращают текст, а не объект JSON. В случае последнего запроса это означает, что нам не нужно сравнивать с JSON объектом (в варианте, когда мы действительно хотим получить строку):
SELECT * FROM json_test WHERE data #>> '{b,c}' = 'd';
id | data
----+--------------------------------------
4 | {"a": 1, "b": {"c": "d", "e": true}}
(1 row)
Таким образом, до этого момента все хорошо. Мы можем работать с разными данными, и те же самые данные могут быть использованы в индексах jsonb тоже. Тем не менее, более внимательный читатель, возможно, заметил, что мы имеем дело с JSON данными, которые имеют путь к объекту от корня. Это не обязательно должно быть так: массивы также валидный JSON, действительно такими являются какие-либо из допустимых примеров:
SELECT
'null'::json,
'true'::json,
'false'::json,
'2'::json,
'1.0001'::json,
'"abc"'::json,
'1E7'::jsonb;
Обратите внимание на последнюю запись, которая является типом jsonb и преобразуется к канонической форме:
json | json | json | json | json | json | jsonb
------+------+-------+------+---------+-------+----------
null | true | false | 2 | 1.00001 | "abc" | 10000000
(1 row)
Так же JSON null отличается от SQL NULL.
Итак, что же происходит, когда мы храним объекты смешанного «типа» в колонке JSON?
INSERT INTO json_test (data)
VALUES ('[]'), ('[1,2,"a"]'), ('null'), ('1E7'), ('"abc"');
SELECT * FROM json_test;
id | data
----+--------------------------------------
1 | {}
2 | {"a": 1}
3 | {"a": 2, "b": ["c", "d"]}
4 | {"a": 1, "b": {"c": "d", "e": true}}
5 | {"b": 2}
6 | []
7 | [1, 2, "a"]
8 | null
9 | 10000000
10 | "abc"
(10 rows)
Вся структура вывелась без проблем. Посмотрим, можем ли мы работать с этими объектами и запросами?
Проверка равенства прекрасно работает:
SELECT * FROM json_test WHERE data = '{"a":1}';
SELECT * FROM json_test WHERE data = 'null';
Ограничения тоже работают, как ожидалось:
SELECT * FROM json_test WHERE data @> '{"a":1}';
SELECT * FROM json_test WHERE data <@ '{"a":1}';
Ключи и существующие элементы тоже работают. Не удивительно, что один запрос будет соответствовать элементам в массиве, а так же ключам в объекте:
SELECT * FROM json_test WHERE data ? 'a';
id | data
----+--------------------------------------
2 | {"a": 1}
3 | {"a": 2, "b": ["c", "d"]}
4 | {"a": 1, "b": {"c": "d", "e": true}}
7 | [1, 2, "a"]
(4 rows)
SELECT * FROM json_test WHERE data ?| array['a', 'b'];
id | data
----+--------------------------------------
2 | {"a": 1}
3 | {"a": 2, "b": ["c", "d"]}
4 | {"a": 1, "b": {"c": "d", "e": true}}
5 | {"b": 2}
7 | [1, 2, "a"]
(5 rows)
SELECT * FROM json_test WHERE data ?& array['a', 'b'];
id | data
----+--------------------------------------
3 | {"a": 2, "b": ["c", "d"]}
4 | {"a": 1, "b": {"c": "d", "e": true}}
(2 rows)
Но как только мы начали делать ключам или элементам 'get' получаем проблемы;
(Видимо у автора статьи на момент её написания был установлен PotgreSQL 9.4 betta версии, поэтому часть запросов сыпали ошибки, проверил на 9.4.1 все запросы отрабатываются):
SELECT * FROM json_test WHERE data ->> 'a' > '1';
ERROR: cannot call jsonb_object_field_text
(jsonb ->> text operator) on an array
Вы по-прежнему можете использовать обход key-path если у вас не скалярные значения:
SELECT * FROM json_test WHERE data #> '{b,c}' = '"d"';
ERROR: cannot call extract path from a scalar
SELECT * FROM json_test WHERE data #> '{b,c}' = '"d"' AND id < 8;
id | data
----+--------------------------------------
4 | {"a": 1, "b": {"c": "d", "e": true}}
(1 row)
Обратите внимание на синтаксис для key path, для строк (должны быть json ключи) или integer (в индексах массивов).
Это накладывает весьма строгие ограничения. Я не знаю, как такие вещи работают в MondgoDB.
Но в перспективе, если вы храните данные в массивах и в объектах json в одной колонке, то в дальшейшем могут быть некоторые проблемы. Но не все потеряно. Можно получить строки на основе базовых объектов:
SELECT * FROM json_test WHERE data @> '{}';
id | data
----+--------------------------------------
1 | {}
2 | {"a": 1}
3 | {"a": 2, "b": ["c", "d"]}
4 | {"a": 1, "b": {"c": "d", "e": true}}
5 | {"b": 2}
(5 rows)
Затем можно этот запрос совместить с запросом выше:
SELECT * FROM json_test WHERE data @> '{}' AND data ->> 'a' > '1';
id | data
----+---------------------------
3 | {"a": 2, "b": ["c", "d"]}
(1 row)
Действительно, в Postgres вам даже не нужно быть уверенным что data @> '{} приходит первым.
Но что делать если нам нужны только array типы данных? Оказывается можно использовать тот же трюк:
SELECT * FROM json_test WHERE data @> '[]';
id | data
----+-------------
6 | []
7 | [1, 2, "a"]
(2 rows)
И это все так же можно сочетать с другими операторами:
SELECT * FROM json_test WHERE data @> '[]' AND data ->> 1 = '2';
id | data
----+-------------
7 | [1, 2, "a"]
(1 row)
Что ж, запись @> оператора доступна только для jsonb столбцов, так что вы не сможете запросить смешанные данные для обычных json колонок.
А что дальше?
Рассмотрение jsonb в Postgres был сторонний проект, сейчас я работаю над json(b) запросами в ORM django. С Django 1.7 в функциях поиска можно будет написать, что-то вроде:
# Exact
MyModel.objects.filter(data={'a': 1})
MyModel.objects.exclude(data={})
# Key/element existence
MyModel.objects.filter(data__has='a')
MyModel.objects.filter(data__has_any=['a', 'b'])
MyModel.objects.filter(data__has_all=['a', 'b'])
# Sub/superset of key/value pair testing
MyModel.objects.filter(data__contains={'a': 1})
MyModel.objects.filter(data__in={'a': 1, 'b': 2})
# Get element/field (compare with json)
MyModel.objects.filter(data__get=(2, {'a': 1}))
# Get element/field (compare with scalar, including gt/lt comparisons)
MyModel.objects.filter(data__get=(2, 'a'))
MyModel.objects.filter(data__get__gt=('a', 1))
# key path traversal, compare with json or scalar.
MyModel.objects.filter(data__get=('{a,2}', {'foo': 'bar'}))
MyModel.objects.filter(data__get=('{a,2}', 2))
MyModel.objects.filter(data__get__lte=('{a,2}', 2))
Но я не уверен, что будут работать имена из последнего набора. Название «get» кажется немного универсальным, и может быть, мы могли использовать разные имена для подстановки входного типа, хотя только integer и string допустимы.