Postgres (12.2) Setup:
CREATE TABLE public.test_table (
id int NOT NULL,
value_type text NOT NULL,
value text NOT NULL
);
INSERT INTO public.test_table
(id, value_type, value)
VALUES (1, 'string', 'a'),
(2, 'json', '{"hello":"world"}'),
(3, 'json', '{"color":"blue"}');
Initial Queries:
select value::jsonb as json_value from test_table where value_type = 'json'
json_value |
------------------|
{"hello": "world"}|
{"color": "blue"} |
But I'm only interested in ones with 'color'.
Moving it to a subquery so that I can get only 'color', also just fine:
select only_json.json_value
from(
select value::jsonb as json_value from test_table where value_type = 'json'
) only_json
where only_json.json_value ? 'color' = true
json_value |
------------------|
{"color": "blue"} |
Now let's break that main table up into two, and suddenly effectively the same query has trouble:
CREATE TABLE public.test_table (
id INT PRIMARY KEY,
value TEXT NOT NULL
);
CREATE TABLE public.test_types (
id INT PRIMARY KEY REFERENCES public.test_table (id),
value_type TEXT NOT NULL
);
INSERT INTO public.test_table
(id, value)
VALUES (1, 'a'),
(2, '{"hello":"world"}'),
(3, '{"color":"blue"}');
insert into public.test_types
(id, value_type)
values (1, 'string'),
(2, 'json'),
(3, 'json');
Now this query:
select id, value from (
select id, value::jsonb from public.test_table natural join public.test_types
where value_type = 'json') only_json
returns, as expected:
id|value |
--|------------------|
2|{"hello": "world"}|
3|{"color": "blue"} |
But as soon as I attach the where clause, it fails:
select id, value from (
select id, value::jsonb from public.test_table natural join public.test_types
where value_type = 'json') only_json
where only_json.value ? 'color' = true
SQL Error [22P02]: ERROR: invalid input syntax for type json
Detail: Token "a" is invalid.
Where: JSON data, line 1: a
It's somehow resurrected the value of 'a' that was well-eliminated prior to this where clause. So what gives? Why does the join cause it to apply the last where clause (which should happen logically last) too early? Failed workarounds I've tried:
- Using left join instead of natural join.
- Applying
where value_type = 'json'to the joined table first, prior to the join. - Moving it to a "with".
- Creating a view and then applying the where clause to a select from the view.
- Creating a column via select called
is_color_holderwithSELECT only_json.value ? 'color' as is_color_holder. This column populates correctly, but if I use a where clause,WHERE is_color_holder = true, I receive the same error. - Repeating the
value_type='json'expression in the problematicwhereclause. - Moving the cast up a subquery.
- Replacing the join with
where id in (select id from public.test_types where value_type = 'json') - Comma-style joins.
- Centering the query around the types table first, then joining the value type after the types have already been filtered.
Is this a bug I should report to postgres? Am I missing something?
Edit: I managed one workaround. See my answer for more details. Still looking for a better answer, though.
select id, value from (
select id, case when value_type = 'json' then value::jsonb else to_jsonb(value) end as value, value_type from
public.test_table natural join public.test_types
where value_type = 'json') as_json
where value ? 'color' = true
id|value |
--|-----------------|
3|{"color": "blue"}|