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