-
Notifications
You must be signed in to change notification settings - Fork 0
/
jsonb_key_exists.sql
205 lines (186 loc) · 9.03 KB
/
jsonb_key_exists.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
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
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
/*
https://dbfiddle.uk/nGQdPkvj
array like jsonb, key exists validate.
find rows containing a key in a JSONB array of objects.
*/
drop table if exists s3 ;
CREATE unlogged TABLE s3 (sid int GENERATED by default as IDENTITY primary key, data jsonb);
INSERT INTO s3(data) VALUES
('{"name": "Somedata",
"array": [{"name":"bla1", "attr": "somevalue"},
{"name":"bla2", "otherdata": "somevalue2"},
{"name":"bla3", "otherdata": "somevalue"}],
"array2": ["bla21","bla20","bla22","bla23"],
"otherstuff": "stuff"}')
-- row with "attr": null
, ( '{"name": "Somedata",
"array": [{"name":"bla0", "attr": null},
{"name":"bla2", "otherdata": "somevalue2"},
{"name":"bla3", "otherdata": "somevalue"}],
"array2": ["bla21","bla20","bla22","bla23"],
"otherstuff": "stuff"}')
-- row with no "attr" key
, ('{"name": "Somedata",
"array": [{"name":"bla0", "non-attr": "somevalue"},
{"name":"bla2", "otherdata": "somevalue2"},
{"name":"bla3", "otherdata": "somevalue"}],
"array2": ["bla21","bla20","bla22","bla23"],
"otherstuff": "stuff"}')
-- with JSON primitive "attr" in array
, ('{"name": "Somedata",
"array": [{"name":"bla0", "non-attr": "somevalue"},
{"name":"bla2", "otherdata": "somevalue2"},
{"name":"bla3", "otherdata": "somevalue"},
"attr"
],
"array2": ["bla21","bla20","bla22","bla23"],
"otherstuff": "stuff"}')
-- with nested array containing "attr" record
, ('{"name": "Somedata",
"array": [{"name":"bla0", "non-attr": "somevalue"},
{"name":"bla2", "otherdata": "somevalue2"},
{"name":"bla3", "otherdata": "somevalue"},
[ {"name":"bla1", "attr": "somevalue"}]],
"array2": ["bla21","bla20","bla22","bla23"],
"otherstuff": "stuff"}')
-- with nested record containing "attr" record
, ('{"array": [{"name":"bla0", "non-attr": "somevalue"},
{"nested": {"name":"bla1", "attr": "somevalue"}}]}')
-- with nested record containing array containing "attr" record
, ('{"array": [{"name":"bla0", "non-attr": "somevalue"},
{"nested": [{"name":"bla1", "attr": "somevalue"}]}]}')
-- with nested record containing array containing array with "attr" primitive
, ('{"array": [{"name":"bla0", "non-attr": "somevalue"},
{"nested": [{"name":"bla1", "foo": ["attr"]}]}]}')
-- with nested record containing array containing array with "attr" key
, ('{"array": [{"name":"bla0", "non-attr": "somevalue"},
{"nested": [{"name":"bla1", "foo": [{"attr":"bar"}]}]}]}')
-- with nested array containing array containing array with "attr" key
, ('{"array": [{"name":"bla0", "non-attr": "somevalue"},
[[[{"attr":"bar"}]]]
]}')
-- with nested array containing record containing array with "attr" key
, ('{"array": [{"name":"bla0", "non-attr": "somevalue"},
[[{"foo":1, "bar":[{"attr":"bar"}]}]]
]}');
CREATE INDEX s3_data_array_idx ON s3 USING GIN ((data->'array')); -- default jsonb_ops
select data
,data @? '$[*] ?(exists (@."attr"))' as array_lax --lax mdoe, $[*] behave the same as $.
--strict mode, if top level is not jsonb array type then NULL.
,data @? 'strict $[*] ?(exists (@."attr"))' as array_strict
,data @? '$?(exists (@."attr"))' as no_array_lax --lax mdoe, $[*] behave the same as $.
--strict mode. jsonb array, path don't have $[] will fail.
,data @? 'strict $?(exists (@."attr"))' as no_array_strict
from (values (jsonb '[{"name":"bla1", "attr": "somevalue"}]'),(jsonb '{"name":"bla1", "attr": "somevalue"}')) s(data);
/*
data | array_lax | array_strict | no_array_lax | no_array_strict
-----------------------------------------+-----------+--------------+--------------+-----------------
[{"attr": "somevalue", "name": "bla1"}] | t | t | t | f
{"attr": "somevalue", "name": "bla1"} | t | [[null]] | t | t
(2 rows)
*/
--jsonb 'lax $.**' produce same result as 'strict $.**'
with cte(data) as(values(jsonb
'{
"track": {
"segments": [
{ "location": [ 47.763, 13.4034 ],"start time": "2018-10-14 10:05:14","HR": 73 },
{ "location": [ 47.706, 13.2635 ],"start time": "2018-10-14 10:39:21","HR": 135 }
]}}'))
select jsonb_path_query(data,'lax $.**') as test_resursive_lax_strict
from cte
except all
select jsonb_path_query(data,'strict $.**') as strict
from cte;
/*
test_resursive_lax_strict
---------------------------
(0 rows)
*/
-- will work, even if the jdata is not array like jsonb.
-- https://www.postgresql.org/docs/current/functions-json.html#FUNCTIONS-SQLJSON-FILTER-EX-TABLE
-- 9.16.2.1. Strict And Lax Modes
-- lax implicit unwrapping can only go one level down within each path evaluation step.
--ok for most case to query if a key exists
select * from s3 where data->'array' @? '$[*] ?(exists (@."attr"))';
--In the strict mode, the specified path must exactly
--match the structure of the queried JSON document to return an SQL/JSON item
select * from s3 where data->'array' @? 'strict $[*] ?(exists (@."attr"))';
-- recursively with the .** acsessor
-- "lax" would be equivalent, but inefficient, doing twice the work
select * from s3 where data->'array' @? '$.** ?(exists (@."attr"))';
-- find values (scalar, primitive) instead of keys
select * from s3 where data->'array' @? '$[*] ? (@=="attr")';
-- find values recursively:
select * from s3 where data ->'array' @? 'strict $.** ? (@ == "attr")';
/*
sid |
data
-----+------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------------
4 | {"name": "Somedata", "array": [{"name": "bla0", "non-attr": "somevalue"}, {"name": "bla2", "otherdata": "somevalue2"},
{"name": "bla3", "otherdata": "somevalue"}, "attr"], "array2": ["bla21", "bla20", "bla22", "bla23"], "otherstuff": "stuff"}
8 | {"array": [{"name": "bla0", "non-attr": "somevalue"}, {"nested": [{"foo": ["attr"], "name": "bla1"}]}]}
(2 rows)
*/
set enable_seqscan to off;
explain(costs off,timing off )
select * from s3 where data->'array' @? 'strict $[*] ?(exists (@."attr"))';
/*
QUERY PLAN
----------------------------------------------------------------------------------------------
Bitmap Heap Scan on s3
Recheck Cond: ((data -> 'array'::text) @? 'strict $[*]?(exists (@."attr"))'::jsonpath)
-> Bitmap Index Scan on s3_data_array_idx
Index Cond: ((data -> 'array'::text) @? 'strict $[*]?(exists (@."attr"))'::jsonpath)
(4 rows)
*/
explain(costs off, timing off)
select * from s3 where data->'array' @? '$[*] ?(exists (@."attr"))';
/*
QUERY PLAN
---------------------------------------------------------------------------------------
Bitmap Heap Scan on s3
Recheck Cond: ((data -> 'array'::text) @? '$[*]?(exists (@."attr"))'::jsonpath)
-> Bitmap Index Scan on s3_data_array_idx
Index Cond: ((data -> 'array'::text) @? '$[*]?(exists (@."attr"))'::jsonpath)
(4 rows)
*/
explain(costs off, timing off )
select * from s3 where data->'array' @? 'strict $.** ? (@ == "attr")';
/*
QUERY PLAN
----------------------------------------------------------------------------------------
Bitmap Heap Scan on s3
Recheck Cond: ((data -> 'array'::text) @? 'strict $.**?(@ == "attr")'::jsonpath)
-> Bitmap Index Scan on s3_data_array_idx
Index Cond: ((data -> 'array'::text) @? 'strict $.**?(@ == "attr")'::jsonpath)
(4 rows)
*/
/*
get keys, mainly for jsonb object and jsonb array of object.
*/
with cte AS
(
select sid
,jsonb_path_query(data->'array','$[*]') as data
,jsonb_typeof(jsonb_path_query(data->'array','$[*]')) as type --top level data type.
from s3
)
,cte1 AS(select sid
,jsonb_path_query(data, 'strict $.**{0 to last}') as elems
,jsonb_typeof(jsonb_path_query(data, 'strict $.**{0 to last}')) as type
from cte
where type = 'array' --if next level is array data type.
),cte2 as (
select distinct on (1,2) sid,jsonb_object_keys(elems) as keys
from cte1
where cte1.type = 'object' --unnest array data type. test if it's object data type.
UNION ALL
select distinct on (1,2) sid,jsonb_object_keys(data)
from cte
where cte.type = 'object' --union top level object keys.
order by 1,2)
select sid, array_agg(keys) as all_keys --aggregaet to get all keys.
from cte2
group by 1;