Postgres 9.6

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 food (
  id int PRIMARY KEY
, name jsonb
, color jsonb
, price jsonb
, description jsonb
);  

INSERT INTO food (id, name, color, price, description) VALUES
  (1, '["Banana"]'::jsonb, '[["Green"]]'::jsonb, '[[[1]]]'::jsonb, '[[["Good"]]]'::jsonb)
, (2, '["Banana"]'::jsonb, '[["Yellow"]]'::jsonb, '[[[]]]'::jsonb, '[[[]]]'::jsonb)
, (3, '["Apple", "Carrot"]'::jsonb, '[["Red"],["Orange"]]'::jsonb, '[[[10]], [[14]]]'::jsonb, '[[["Round"]],[["Long"]]]'::jsonb)
, (4, '["Grape", "Pineapple"]'::jsonb, '[["Green"],["Brown", "Pink"]]'::jsonb, '[[[2]], [[13], [29]]]'::jsonb, '[[["Small"]], [["Short"], ["Tall"]]]'::jsonb)
, (5, '["Orange", "Potato"]'::jsonb, '[["Orange"],["Purple", "Brown"]]'::jsonb, '[[[2]], [[9], []]]'::jsonb, '[[["Bright"]], [["Gross"], []]]'::jsonb)
RETURNING *
;


id
name
color
price
description


1
["Banana"]
[["Green"]]
[[[1]]]
[[["Good"]]]

2
["Banana"]
[["Yellow"]]
[[[]]]
[[[]]]

3
["Apple", "Carrot"]
[["Red"], ["Orange"]]
[[[10]], [[14]]]
[[["Round"]], [["Long"]]]

4
["Grape", "Pineapple"]
[["Green"], ["Brown", "Pink"]]
[[[2]], [[13], [29]]]
[[["Small"]], [["Short"], ["Tall"]]]

5
["Orange", "Potato"]
[["Orange"], ["Purple", "Brown"]]
[[[2]], [[9], []]]
[[["Bright"]], [["Gross"], []]]



INSERT 0 5


CREATE OR REPLACE FUNCTION f_jsonb_array_elements_if_any(jsonb)
  RETURNS SETOF jsonb
  LANGUAGE plpgsql IMMUTABLE STRICT PARALLEL SAFE AS
$func$
BEGIN
   IF jsonb_typeof($1) = 'array' THEN 
      IF $1 = '[]' THEN
         RETURN NEXT jsonb 'null';
      ELSE
         RETURN QUERY SELECT jsonb_array_elements($1);
      END IF;
   ELSE
      RETURN NEXT $1;
   END IF;
END
$func$;

CREATE FUNCTION


-- happens to work in Postgrres 9.6  (breaks in later versions)
WITH RECURSIVE cte AS (
   SELECT 0 AS lvl, id
        , f_jsonb_array_elements_if_any(name)        AS n
        , f_jsonb_array_elements_if_any(color)       AS c
        , f_jsonb_array_elements_if_any(price)       AS p
        , f_jsonb_array_elements_if_any(description) AS d
   FROM   food

   UNION ALL
   SELECT lvl + 1, id
        , f_jsonb_array_elements_if_any(n) AS n
        , f_jsonb_array_elements_if_any(c) AS c
        , f_jsonb_array_elements_if_any(p) AS p
        , f_jsonb_array_elements_if_any(d) AS d
   FROM   cte   
   WHERE  'array' IN (jsonb_typeof(n), jsonb_typeof(c), jsonb_typeof(p) , jsonb_typeof(d))
   )
SELECT id, n->>0 AS name, c->>0 AS color, p->>0 AS price, d->>0 AS description
FROM  (
   SELECT rank() OVER (PARTITION BY id ORDER BY lvl DESC) AS rnk
        , *
   FROM   cte c
   ) sub
WHERE  rnk = 1;


id
name
color
price
description


1
Banana
Green
1
Good

2
Banana
Yellow
null
null

3
Carrot
Orange
14
Long

3
Apple
Red
10
Round

4
Grape
Green
2
Small

4
Pineapple
Pink
29
Tall

4
Pineapple
Brown
13
Short

5
Potato
Brown
null
null

5
Orange
Orange
2
Bright

5
Potato
Purple
9
Gross



SELECT 10


-- Your original query also happens to work in Postgrres 9.6 (breaks in later versions)
WITH first_level AS (
  SELECT
      id,
      jsonb_array_elements(name) AS name,
      jsonb_array_elements(color) AS color,
      jsonb_array_elements(price) AS price,
      jsonb_array_elements(description) AS description
  FROM food
),
second_level AS (
SELECT
      id,
      name,
      jsonb_array_elements(color) AS color,
      jsonb_array_elements(price) AS price,
      jsonb_array_elements(description) AS description
  FROM first_level
  )
SELECT
      id,
      name,
      color,
      CASE
            WHEN price = '[]'::jsonb THEN null
            ELSE jsonb_array_elements(price)
      END AS price,
      CASE
            WHEN description = '[]'::jsonb THEN null
            ELSE jsonb_array_elements(description)
      END AS description
FROM second_level


id
name
color
price
description


1
"Banana"
"Green"
1
"Good"

2
"Banana"
"Yellow"
null
null

3
"Apple"
"Red"
10
"Round"

3
"Carrot"
"Orange"
14
"Long"

4
"Grape"
"Green"
2
"Small"

4
"Pineapple"
"Brown"
13
"Short"

4
"Pineapple"
"Pink"
29
"Tall"

5
"Orange"
"Orange"
2
"Bright"

5
"Potato"
"Purple"
9
"Gross"

5
"Potato"
"Brown"
null
null



SELECT 10

id	name	color	price	description
1	["Banana"]	[["Green"]]	[[[1]]]	[[["Good"]]]
2	["Banana"]	[["Yellow"]]	[[[]]]	[[[]]]
3	["Apple", "Carrot"]	[["Red"], ["Orange"]]	[[[10]], [[14]]]	[[["Round"]], [["Long"]]]
4	["Grape", "Pineapple"]	[["Green"], ["Brown", "Pink"]]	[[[2]], [[13], [29]]]	[[["Small"]], [["Short"], ["Tall"]]]
5	["Orange", "Potato"]	[["Orange"], ["Purple", "Brown"]]	[[[2]], [[9], []]]	[[["Bright"]], [["Gross"], []]]

id	name	color	price	description
1	Banana	Green	1	Good
2	Banana	Yellow	null	null
3	Carrot	Orange	14	Long
3	Apple	Red	10	Round
4	Grape	Green	2	Small
4	Pineapple	Pink	29	Tall
4	Pineapple	Brown	13	Short
5	Potato	Brown	null	null
5	Orange	Orange	2	Bright
5	Potato	Purple	9	Gross

id	name	color	price	description
1	"Banana"	"Green"	1	"Good"
2	"Banana"	"Yellow"	null	null
3	"Apple"	"Red"	10	"Round"
3	"Carrot"	"Orange"	14	"Long"
4	"Grape"	"Green"	2	"Small"
4	"Pineapple"	"Brown"	13	"Short"
4	"Pineapple"	"Pink"	29	"Tall"
5	"Orange"	"Orange"	2	"Bright"
5	"Potato"	"Purple"	9	"Gross"
5	"Potato"	"Brown"	null	null