Postgres 10

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( id INT, jsdata JSONB)
INSERT INTO t VALUES
(1, '{
"test_question_number": ["1000000000", "5000000000"],
"question1": 0.04975124378109453,
"question2": 5.077114427860696,
"question3": 75621.89054726369,
"question4": 3482.587064676617,
"question6": 1,
"question8": 0.000176068
}');
1 rows affected

WITH t AS
(
  SELECT JSONB_TYPEOF(value::JSONB) AS type, js.*
    FROM t
   CROSS JOIN JSONB_EACH(jsdata) AS js
)
SELECT arr.*, question1, question2, question3, question4, question6, question8
  FROM
  (
   SELECT row_id, test_question_number 
     FROM t
    CROSS JOIN JSONB_ARRAY_ELEMENTS(value::JSONB) 
     WITH ORDINALITY arr(test_question_number,row_id)
    WHERE type = 'array' ) AS arr
  LEFT JOIN
  ( SELECT cnt, MAX(value::text) FILTER (WHERE key = 'question1') AS question1,
                MAX(value::text) FILTER (WHERE key = 'question2') AS question2,
                MAX(value::text) FILTER (WHERE key = 'question3') AS question3,
                MAX(value::text) FILTER (WHERE key = 'question4') AS question4,
                MAX(value::text) FILTER (WHERE key = 'question6') AS question6,
                MAX(value::text) FILTER (WHERE key = 'question8') AS question8
      FROM (SELECT t.*, COUNT(*) OVER (PARTITION BY key) AS cnt
              FROM t
             WHERE type != 'array' 
           ) AS q
     GROUP BY cnt ) AS obj
        ON arr.row_id = obj.cnt 
  


row_id
test_question_number
question1
question2
question3
question4
question6
question8


1
"1000000000"
0.04975124378109453
5.077114427860696
75621.89054726369
3482.587064676617
1
0.000176068

2
"5000000000"
null
null
null
null
null
null

row_id	test_question_number	question1	question2	question3	question4	question6	question8
1	"1000000000"	0.04975124378109453	5.077114427860696	75621.89054726369	3482.587064676617	1	0.000176068
2	"5000000000"	null	null	null	null	null	null