Ранее я писал, как включить поддержку 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)
SELECT * FROM json_test WHERE data = '{"a":1}';
id | data
----+------
1 | {"a": 1}
(1 row)
SELECT * FROM json_test WHERE data @> '{"a":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)
SELECT * FROM json_test WHERE data ->> '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)
SELECT * FROM json_test WHERE data #> '{b,c}' = '"d"';
id | data
----+--------------------------------------
4 | {"a": 1, "b": {"c": "d", "e": true}}
SELECT * FROM json_test WHERE data #>> '{b,c}' = 'd';
id | data
----+--------------------------------------
4 | {"a": 1, "b": {"c": "d", "e": true}}
(1 row)
SELECT
'null'::json,
'true'::json,
'false'::json,
'2'::json,
'1.0001'::json,
'"abc"'::json,
'1E7'::jsonb;
json | json | json | json | json | json | jsonb
------+------+-------+------+---------+-------+----------
null | true | false | 2 | 1.00001 | "abc" | 10000000
(1 row)
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)
SELECT * FROM json_test WHERE data ->> 'a' > '1';
ERROR: cannot call jsonb_object_field_text
(jsonb ->> text operator) on an array
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)
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)
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)
# 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))
К сожалению, не доступен сервер mySQL