0

sql is not my strong point

upgrading from postgres 9.6 to 10.6 and I get an error from this query:

SELECT id, (CASE WHEN jsonb_typeof(content->'user')='array' THEN jsonb_array_elements(content -> 'user')
         ELSE content::jsonb->'user' END) As att FROM event;

error: set returning functions are not allowed in CASE

result i get back (from v 9.6):

id, att
1, {"name": "Andrew"},
2, {"name": "Stacey"},
3, null

the 'user' element can also be null.

This is what a 'content' column could look like (either one of the two)

{"user": [{"name": "Andrew", "country_code": "GBR"}]]
{"user": null}

any help pleas been stuck on this for a while

Brunaldo
  • 324
  • 3
  • 11

1 Answers1

1

I guess you could use

SELECT id, att
FROM event,
jsonb_array_elements(CASE WHEN jsonb_typeof(content->'user')='array'
  THEN content -> 'user'
  ELSE jsonb_build_array(content->'user')
END) AS user(att);
Bergi
  • 630,263
  • 148
  • 957
  • 1,375