Reputation: 7770
Table Schema:
create table test_table
(
id serial not null,
data jsonb
);
Sample Data:
INSERT INTO public.test_table (id, data) VALUES (1, '[{"": "VALUE1", "KEY2": "VALUE2"}, {"KEY1": "VALUE3", "KEY3": "VALUE4"}]');
INSERT INTO public.test_table (id, data) VALUES (2, '[{"''KEY1 ''": "VALUE1", "KEY2": "VALUE2"}, {"KEY3": "VALUE3", "KEY4": "VALUE4"}]');
SQL Query:
SELECT id, arr_elem
FROM test_table AS tt, jsonb_array_elements(
(
SELECT data
FROM test_table
WHERE id = tt.id
)
) AS arr_elem
WHERE arr_elem#>'{KEY1}' IS NOT NULL
I would like to tune to above query to match the following scenarios:
"": "VALUE1"
"''": "VALUE1"
"'KEY1 '": "VALUE1"
Tried to escape the quotes and spaces and the query didn't return the expected results.
UPDATE 1:
Solution for 1: http://sqlfiddle.com/#!17/6d431/20
SELECT id, arr_elem
FROM test_table AS tt, jsonb_array_elements(
(
SELECT data
FROM test_table
WHERE id = tt.id
)
) AS arr_elem
WHERE arr_elem->'' IS NOT NULL
Upvotes: 0
Views: 2435
Reputation: 5982
Another answer that's more appropriate for the json "array of objects" you have:
select * from test_table
where exists
(select * from jsonb_array_elements(data)
where exists
(select * from
(select jsonb_object_keys as k from
jsonb_object_keys(jsonb_array_elements)
) x
where x.k ='' or x.k ='''''' or x.k like '% '''
)
);
Note: the inner most (select json_object_keys as k...) shouldn't actually be necessary syntactically, but I never could get it to work as a smaller "one liner".
Also, this query selects all 3 conditions at once. You can easily adjust it to weed out these scenarios or do them one at a time by adjusting the 3 "or" sections of the where clause...
Explanation: you've got to first expand the data column to an array of objects using jsonb_array_elements to get each object, then turn, each of those objects has to be expanded to an array of keys, which can then be searched as normal text.
Upvotes: 0
Reputation: 5982
select * from test_table
where data->'' is not null /*gets rows with blank key names;
select * from test_table
where data->'''''' is not null; /*gets rows with '' as a key name;
The last is a bit more complex...
select * from test_table
where exists
(select * from json_each(data) where key != rtrim(key))
This past one gets the row where any key != rtrim(key), so that should get items with trailing spaces in key names.
Upvotes: 1