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