Let's consider that I have a table that has a multivaluated column of type json that holds arrays. So in order to normalize the relation one could do:
select
id,
description,
json_array_elements(letters)
from
(
values
(1, 'good', '["a","b", "c"]'::json),
(2, 'bad', '[]'::json)
) as aTable(id,description,letters);
(Ready-to-run example here)
The thing here is that the row with id 2 is NOT listed as the array is empty (or null).
So I do wonder what kind of thing I have to do in order to get that row with a mock|default|hardcoded value... for example I have try this:
select
id,
description,
CASE
WHEN json_array_length(letters) = 0 THEN '"x"'::json
ELSE json_array_elements(letters)
END
from
(
values
(1, 'good', '["a","b", "c"]'::json),
(2, 'bad', '[]'::json)
) as aTable(id,description,letters)
(RTR example here)
And postgres states that is not possible as
ERROR: set-returning functions are not allowed in CASE
LINE 6: ELSE json_array_elements(letters)
^
HINT: You might be able to move the set-returning function into a LATERAL FROM item.
I have tried the HINT but also has no results...
select
id,
description,
l.letter
from
(
values
(1, 'good', '["a","b", "c"]'::json),
(2, 'bad', '[]'::json)
) as aTable(id,description,letters)
CROSS JOIN LATERAL (
SELECT
CASE
WHEN json_array_length(letters) = 0 THEN '"x"'::json
ELSE json_array_elements(letters)
END
) as l(letter)
So problably I'm not getting the HINT...
I do wonder if someone can help me with this.
Thanks
Víctor
An alternative is to substitute a json array of "x":
select
id,
description,
json_array_elements(CASE WHEN json_array_length(letters) = 0 THEN '["x"]'::json ELSE letters END)
from
(
values
(1, 'good', '["a","b", "c"]'::json),
(2, 'bad', '[]'::json)
) as aTable(id,description,letters);