test.sql

application/octet-stream

Download

create table tb2 (id int, a jsonb);
insert into tb2 values (1, '{"a": 1, "b": true, "c": {"d": 2}}'::jsonb), (2, '[1, 2, 3]'::jsonb), (3, '1'::jsonb);

\pset null <null>

---------------------------                                                                                                                                                                                                                                                   
-- jsonb_object_field  ----                                                                                                                                                                                                                                                   
---------------------------                                                                                                                                                                                                                                                   

explain verbose select (a->'a')::numeric, (a->'a')::int2, (a->'a')::int4, (a->'a')::int8, (a->'a')::float4, (a->'a')::float8, (a->'b')::bool, (a->'notexists')::int2 from tb2 where id = 1;
select (a->'a')::numeric, (a->'a')::int2, (a->'a')::int4, (a->'a')::int8, (a->'a')::float4, (a->'a')::float8, (a->'b')::bool, (a->'notexists')::int2 from tb2 where id = 1;

select (a->'a')::bool from tb2 where id = 1;

select (a->'b')::int2 from tb2 where id = 1;

select (a->'c')::numeric from tb2 where id = 1;

select (a->'a')::numeric, (a->'a')::int2, (a->'a')::int4, (a->'a')::int8, (a->'a')::float4, (a->'a')::float8, (a->'b')::bool, (a->'notexists')::int2 from tb2 where id = 2;

---------------------------                                                                                                                                                                                                                                                   
-- jsonb_extract_path -----                                                                                                                                                                                                                                                   
---------------------------                                                                                                                                                                                                                                                   

-- all the normal cases                                                                                                                                                                                                                                                       
select (a #> '{"a"}')::numeric,  (a #> '{"a", "b"}')::numeric,  (a #> '{"c", "d"}')::numeric from tb2;

-- field doesn't exists                                                                                                                                                                                                                                                       
select (a #> '{"a", "b"}')::numeric from tb2;
select (a #> '{"notexists"}')::numeric from tb2;

---------------------------                                                                                                                                                                                                                                                   
-- jsonb_array_element-----                                                                                                                                                                                                                                                   
---------------------------                                                                                                                                                                                                                                                   

explain verbose select a,  (a->0) as idx0, (a->0)::numeric from tb2;

select a,  (a->0) as idx0, (a->0)::numeric from tb2;

-- out of range case.                                                                                                                                                                                                                                                         
select a, a->1, (a->1)::numeric from tb2;