PostgreSQL error: CASE with JSON set-returning functions

Question

I've got a SELECT statement with a CASE that uses JSON functions, which stopped working when upgrading to PostgreSQL 10.

SELECT
        CASE 
            WHEN type = 'a' THEN data #>> '{key_a,0}'
            WHEN type = 'b' THEN data #>> '{key_a,0,key_b,0}'
            WHEN type = 'c' THEN jsonb_object_keys(data #> '{key_c,key_d}')
            ELSE NULL
        END AS foo,
        CASE
            WHEN type = 'a' THEN jsonb_array_elements_text(data -> 'key_e')
            WHEN type = 'b' THEN data #>> '{key_f,0,key_g}'
            ELSE NULL
        END AS bar

ERROR: set-returning functions are not allowed in CASE

Hint: You might be able to move the set-returning function into a LATERAL FROM item.

I don't understand how using a LATERAL FROM is an alternative. In fact, I don't fully understand why LATERAL would be used, even when reading the PG docs (section 7.2.1.5).

Any ideas how this statement would be converted to work with PG 10?

As long as neither of those functions will ever return more than 1 row (which only you will know based on your data), you can just put them in a sub-select: THEN (SELECT jsonb_object_keys(data #> '{key_c,key_d}')) — 404
@eurotrash that works in my situation, thanks! If you move your comment to an answer, I'd be happy to accept it. — Dennis

404 404 · Accepted Answer · 2018-09-18T11:06:30

As long as none of those set-returning functions will return more than one row, you can put them in a subquery to get around the restriction:

SELECT
        CASE 
            WHEN type = 'a' THEN data #>> '{key_a,0}'
            WHEN type = 'b' THEN data #>> '{key_a,0,key_b,0}'
            WHEN type = 'c' THEN (SELECT jsonb_object_keys(data #> '{key_c,key_d}'))
            ELSE NULL
        END AS foo,
        CASE
            WHEN type = 'a' THEN (SELECT jsonb_array_elements_text(data -> 'key_e'))
            WHEN type = 'b' THEN data #>> '{key_f,0,key_g}'
            ELSE NULL
        END AS bar

PostgreSQL error: CASE with JSON set-returning functions

1 Answers