Postgres 12

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select true > false


?column?


t


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');
3 rows affected

3 rows affected

select id, value from (
    select id, value 
    from public.test_table 
    inner join public.test_types using(id)
    where value_type = 'json'
) only_json
where id <> -1 and value::jsonb ? 'color'
ERROR:  invalid input syntax for type json
DETAIL:  Token "a" is invalid.
CONTEXT:  JSON data, line 1: a


select id, value from (
    select id, value 
    from public.test_table 
    inner join public.test_types using(id)
    where value_type = 'json'
    offset 0
) only_json
where id <> -1 and value::jsonb ? 'color'


id
value


3
{"color":"blue"}