An SQL query has to return a fixed number of columns. The return type has to be known at call time (at the latest). Number, names and data types of columns in the returned row(s) are fixed by then. There is no way to get a truly dynamic number of result columns in SQL. You'd have to use two steps (two round trips to the DB server):
- Determine the list or result columns.
- Send a query to produce that result.
Notably, that leaves a time window for race conditions under concurrent write load.
Typically, it's simpler to just return an array or a list or a document type (like JSON) for a variable number of values. Or a set of rows.
If there is a low, well-known maximum of possible values, say 6, like in your added example, just over-provision:
SELECT id
, js_line_item ->> 'type' AS type
, js_line_item ->> 'status' AS status
, js_line_item #>> '{permissions, 1}' AS perm1
, js_line_item #>> '{permissions, 2}' AS perm2
-- , ...
, js_line_item #>> '{permissions, 6}' AS perm6
FROM newtable n
LEFT JOIN LATERAL jsonb_array_elements(n.column1 -> 'usertype') AS js_line_item ON true;
LEFT JOIN
to retain rows without any permissions.
与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…