postgresqlmultivalue

Postgres - Dealing with empty set of elements


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


Solution

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