需要帮助将CASE中的返回集函数移动到LATERAL连接中

Question

我有一个应用程序查询，需要重写，以便数据库可以升级。我已经尝试了几件事在这里列出的论坛，如移动集返回函数到横向连接，但似乎不能得到它的工作。任何帮助都会很感激。

SELECT id, name, description, owner, is_private, read_scopes, edit_scopes,
CASE 
WHEN jsonb_typeof(json_element) = 'array' 
THEN jsonb_array_elements(json_element)
WHEN jsonb_exists(json_element, 'children')
THEN jsonb_array_elements(json_element -> 'children')
END AS json_element
FROM children
WHERE  jsonb_typeof(json_element) = 'array' OR jsonb_typeof(json_element) = 'object'

Answer 1

您可以将决定将哪个数组拆套到传递给jsonb_array_elements()的参数中:

select id, name, j.*
from children c
cross join jsonb_array_elements(case 
when jsonb_typeof(c.json_element) = 'array' then json_element 
when jsonb_typeof(c.json_element -> 'children') = 'array' then json_element -> 'children'
end) as j(json_element)
where jsonb_typeof(c.json_element) = 'array'
or jsonb_typeof(c.json_element -> 'children') = 'array'