[Перевод] JSONB запросы в PostgreSQL
Ранее я писал, как включить поддержку 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 допустимы.
