JSONField Lookups and Transforms
 Share
The version of the browser you are using is no longer supported. Please upgrade to a supported browser.Dismiss

Comment only
 
 
ABCDEFGHIJKLMNOPQRSTUVWXYZ
1
JSONField LookupsFunction/operator used in implementation
2
NameDescriptionExamplePostgreSQLMySQLMariaDBOracleSQLite
3
containsDoes the data contain the right JSON path/value entries at the top level?data__contains={'owner': 'Bob'}@>JSON_CONTAINSJSON_CONTAINSJSON_QUERY and JSON_VALUEJSON_EXTRACT and JSON_TYPE
4
contained_by
Are the data path/value entries contained at the top level within the right JSON value? (inverse of contains)
data__contained_by={'breed': 'collie', 'owner': 'Bob'}
<@JSON_CONTAINSJSON_CONTAINSNot applicableNot applicable
5
has_keyDoes the string exist as a top-level key within the JSON value?data__has_key='owner'?JSON_CONTAINS_PATHJSON_CONTAINS_PATHJSON_EXISTS
JSON_TYPE(table.data, $.name) IS NOT NULL
6
has_any_keysDo any of these array strings exist as top-level keys?data__has_any_keys=['owner', 'breed']?|JSON_CONTAINS_PATHJSON_CONTAINS_PATHJSON_EXISTSlike has_key, OR-ed together
7
has_keysDo all of these array strings exist as top-level keys?data__has_keys=['breed', 'owner']?&JSON_CONTAINS_PATHJSON_CONTAINS_PATHJSON_EXISTSlike has_key, AND-ed together
8
9
JSONField TransformsFunction/operator used in implementation
10
NameDescriptionExamplePostgreSQLMySQLMariaDBOracleSQLite
11
keyGet JSON object field by keydata__breed='collie'->JSON_EXTRACTJSON_EXTRACTJSON_QUERY and JSON_VALUEJSON_EXTRACT and JSON_TYPE
12
indexGet JSON array element (indexed from zero, negative integers count from the end)data__owner__other_pets__0__name='Fishy'->JSON_EXTRACTJSON_EXTRACTJSON_QUERY and JSON_VALUEJSON_EXTRACT and JSON_TYPE
13
pathGet JSON object at specified path (chained key transforms)data__owner__name='Bob'#>JSON_EXTRACTJSON_EXTRACTJSON_QUERY and JSON_VALUEJSON_EXTRACT and JSON_TYPE
14
15
Useful links
16
Querying django.contrib.postgres.fields.JSONField
17
PostgreSQL JSON data type
18
PostgreSQL functions and operators
19
SQLite JSON1 extension
20
MySQL Searching and Modifying JSON Values
21
MySQL JSON Function Reference
22
MySQL Functions That Search JSON Values
23
MariaDB JSON Data Type
24
MariaDB JSON Functions
25
Oracle SQL Functions and Conditions for Use with JSON Data
26
27
Notes
28
The above lookups and transforms are based on what's currently available in django.contrib.postgres.
29
Current implementation provided in oracle_json_field package only has Transforms (combined with builtin lookups).
30
oracle_json_field package uses dot-notation syntax for its KeyTransform and make use of custom Queryset and Manager to force a self-join so table aliases (mandatory) will be applied.
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
Loading...