Postgres - Dealing with empty set of elements

Question

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

Answer 1

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);

Answer 2

This can be done using left join with a condition on true

with cte(id,description,letters) as (
  values
    (1, 'good', '["a","b", "c"]'::json),
    (2, 'bad', '[]'::json)
)
select id,description, COALESCE(l.value::text, 'x') as letter
from cte
left join json_array_elements_text(letters) l on true

Its also can be done using :

with cte(id,description,letters) as (
  values
    (1, 'good', '["a","b", "c"]'::json),
    (2, 'bad', '[]'::json)
)
select id,description, COALESCE(l.value::text, 'x') as letter
from cte,
json_array_elements_text(case when letters::text = '[]' then '[null]'::json else letters end) l;

json_array_elements_text('[null]') returns null,

json_array_elements_text('[]') does not return anything

Demo here