Postgres 15

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.
select jsonb_pretty('{"k1":"v1", "k2":["v21","v22","v23"]}'::jsonb);

jsonb_pretty

{
    "k1": "v1",
    "k2": [
        "v21",
        "v22",
        "v23"
    ]
}

SELECT 1

--not allowed
select "k2" 
from '{"k1":"v1", "k2":["v21","v22","v23"]}'::jsonb;

ERROR:  syntax error at or near "'{"k1":"v1", "k2":["v21","v22","v23"]}'"
LINE 3: from '{"k1":"v1", "k2":["v21","v22","v23"]}'::jsonb;
             ^

--kind of makes sense
select "value" 
from jsonb_each('{"k1":"v1", "k2":["v21","v22","v23"]}'::jsonb) 
where "key"='k2';

value

["v21", "v22", "v23"]

SELECT 1

--access by subscript
select ('{"k1":"v1", "k2":["v21","v22","v23"]}'::jsonb)['k2'];

jsonb

["v21", "v22", "v23"]

SELECT 1

select (select "value" 
from jsonb_each('{"k1":"v1", "k2":["v21","v22","v23"]}'::jsonb) 
where "key"='k2') ? 'v22';

?column?

t

SELECT 1

select ('{"k1":"v1", "k2":["v21","v22","v23"]}'::jsonb)['k2'] ? 'v22';

?column?

t

SELECT 1

select jsonb_path_exists('{"k1":"v1", "k2":["v21","v22","v23"]}'::jsonb,
                         '$.k2[*] ? (@==$var)',
                         '{"var":"v22"}');

jsonb_path_exists

t

SELECT 1

DO $$
DECLARE PAYLOAD jsonb;
BEGIN
  SELECT '{"k1":"v1", "k2":["v21","v22","v23"]}'::jsonb into PAYLOAD;
  CREATE TABLE test AS SELECT "PAYLOAD";--not allowed
END $$;

ERROR:  column "PAYLOAD" does not exist
LINE 1: CREATE TABLE test AS SELECT "PAYLOAD"
                                    ^
QUERY:  CREATE TABLE test AS SELECT "PAYLOAD"
CONTEXT:  PL/pgSQL function inline_code_block line 5 at SQL statement

DO $$
DECLARE PAYLOAD jsonb;
BEGIN
  SELECT '{"k1":"v1", "k2":["v21","v22","v23"]}'::jsonb into PAYLOAD;
  CREATE TABLE test AS SELECT "k2" FROM "PAYLOAD";--not allowed either
END $$;

ERROR:  relation "PAYLOAD" does not exist
LINE 1: CREATE TABLE test AS SELECT "k2" FROM "PAYLOAD"
                                              ^
QUERY:  CREATE TABLE test AS SELECT "k2" FROM "PAYLOAD"
CONTEXT:  PL/pgSQL function inline_code_block line 5 at SQL statement

DO $$
DECLARE PAYLOAD jsonb;
BEGIN
  SELECT '{"k1":"v1", "k2":["v21","v22","v23"]}'::jsonb INTO PAYLOAD;
  CREATE TABLE test AS SELECT PAYLOAD['k2'];--OK
END $$;

SELECT * FROM test;

DO

payload

["v21", "v22", "v23"]

SELECT 1