Postgres 13

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?.

CREATE TABLE tbl (id int, json jsonb);
INSERT INTO tbl VALUES
  (1, '{"key":{"1": 0, "2": 3, "5": 0}}')
, (2, '{"key":{"2": 4, "5": 8, "6": 0}}')   -- additional key
, (3, '{"key":{}}')                         -- empty object
, (3, '{"nokey":{"2": 4, "5": 8, "6": 0}}') -- nothing found
;
4 rows affected

-- return jsonb object
SELECT jsonb '{"1": 15, "2": 15, "3": 15, "4": 15, "5": 15}' || (json->'key')
FROM   tbl;


?column?


{"1": 0, "2": 3, "3": 15, "4": 15, "5": 0}

{"1": 15, "2": 4, "3": 15, "4": 15, "5": 8, "6": 0}

{"1": 15, "2": 15, "3": 15, "4": 15, "5": 15}

null


-- return int[]
WITH a(key) AS (SELECT generate_series(1, 5)::text)
SELECT ARRAY (SELECT COALESCE(value::int, 15)
              FROM   jsonb_each_text(t.json->'key') RIGHT JOIN a USING (key)
              ORDER  BY key) AS int_arr
FROM   tbl t;


int_arr


{0,3,15,15,0}

{15,4,15,15,8}

{15,15,15,15,15}

{15,15,15,15,15}