Ранее я писал, как включить поддержку 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 допустимы.
ссылка на оригинал статьи http://habrahabr.ru/post/254425/