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
– 404, Commented Sep 18, 2018 at 10:19
@eurotrash that works in my situation, thanks! If you move your comment to an answer, I'd be happy to accept it. — Dennis
– Dennis, Commented Sep 18, 2018 at 10:55

404 · Accepted Answer · 2018-09-18 11:06:30Z

3

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

answered Sep 18, 2018 at 11:06

404

8,7622 gold badges33 silver badges53 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

PostgreSQL error: CASE with JSON set-returning functions

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related