Can't perform json operations in where clause on filtered and then casted text when a join is used for the filtering. Where clause executes too early

Viewed 100

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_holder with SELECT 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 problematic where clause.
  • 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"}|
2 Answers

I suspect that what you are seeing is premature optimization, caused by predicate pushdown.

In Postgres, a common strategy to avoid that is the offset 0 hack:

select id, value from (
    select id, value 
    from public.test_table 
    inner join public.test_types using(id)
    where value_type = 'json'
    offset 0       -- (try to) prevent predicate pushdown
) only_json
where value::jsonb ? 'color'

Demo on DB Fiddle

I found a workaround. I'll post as an 'answer' here and edit the above question.

But if anyone has a better answer for me, I'll make yours as the correct one.

Casting non-json values to json with "CASE" works fine:

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"}|
Related