Postgres 12

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 t( jsdata JSONB )
INSERT INTO t VALUES
('{
  "C1": [
    {
      "id": 13371,
      "class": "class_A1",
      "inputs": {
        "input_A1": 403096
      },
      "outputs": {
        "output_A1": 403097
      }
    },
    {
      "id": 10200,
      "class": "class_A2",
      "inputs": {
        "input_A2_1": 403096,
        "input_A2_2": 403095
      },
      "outputs": {
        "output_A2": [
          [
            403098,
            {
              "output_A2_1": 403101
            },
            {
              "output_A2_2": [
                403099,
                403100
              ]
            }
          ]
        ],
        "output_A2_3": 403102,
        "output_A2_4": 403103,
        "output_A2_5": 403104
      }
    }
  ]
}')
1 rows affected

WITH j AS
(
SELECT j2.*, JSONB_TYPEOF(j2.value) AS type
  FROM t,
  JSONB_EACH(jsdata) AS j0(k,v),
  JSONB_ARRAY_ELEMENTS(v) AS j1,
  JSONB_EACH((j1.value ->> 'outputs')::JSONB) AS j2
), jj AS
(
SELECT key,j1.*,JSONB_TYPEOF(j1.value::JSONB) AS type
  FROM j,
  JSONB_ARRAY_ELEMENTS(value) AS j0(v),
  JSONB_ARRAY_ELEMENTS(v) AS j1
 WHERE type = 'array'  
), jjj AS
(
 SELECT key,j0.v,JSONB_TYPEOF(j0.v::JSONB) AS type,k
   FROM jj,
   JSONB_EACH(value) AS j0(k,v)
  WHERE type IN ('array','object') 
)
SELECT key,value
  FROM
  (
   SELECT key,value,type
     FROM j  
    UNION ALL
   SELECT key,value,type
     FROM jj
    UNION ALL
   SELECT k,v,type
     FROM jjj
   ) jt  
WHERE type NOT IN ('array','object')  
UNION ALL 
SELECT k,value
  FROM jjj,JSONB_ARRAY_ELEMENTS(v) AS j0
 WHERE type IN ('array','object') 


key
value


output_A1
403097

output_A2_3
403102

output_A2_4
403103

output_A2_5
403104

output_A2
403098

output_A2_1
403101

output_A2_2
403099

output_A2_2
403100

key	value
output_A1	403097
output_A2_3	403102
output_A2_4	403103
output_A2_5	403104
output_A2	403098
output_A2_1	403101
output_A2_2	403099
output_A2_2	403100