Postgres 11

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 test
(
  id integer NOT NULL DEFAULT 1,
  testcol jsonb
)

CREATE TABLE


insert into test (testcol) values 
('[{"type": {"value": 1, "displayName": "flag1"}, "value": "10"},
{"type": {"value": 2, "displayName": "flag2"}, "value": "20"}, 
{"type": {"value": 3, "displayName": "flag3"}, "value": "30"},
 {"type": {"value": 4, "displayName": "flag4"}},
 {"type": {"value": 4, "displayName": "flag4"}},
 {"type": {"value": 6, "displayName": "flag6"}, "value": "40"}]');

INSERT 0 1


WITH cte AS (
  SELECT  test.id, sub."type"->'value' AS t_value, sub."type"->'displayName' AS t_name, value
  FROM test
  ,LATERAL jsonb_to_recordset(testcol) sub("type" jsonb, "value" int)
)
SELECT *
FROM cte
-- WHERE ..


id
t_value
t_name
value


1
1
"flag1"
10

1
2
"flag2"
20

1
3
"flag3"
30

1
4
"flag4"
null

1
4
"flag4"
null

1
6
"flag6"
40



SELECT 6

id	t_value	t_name	value
1	1	"flag1"	10
1	2	"flag2"	20
1	3	"flag3"	30
1	4	"flag4"	null
1	4	"flag4"	null
1	6	"flag6"	40