sql - How to query a nested json with varying key value pairs

Question

My previous question has been answered, thanks to @Erwin Brandstetter for the help:

Query individual values in a nested json record

I have a follow-up:

Aurora Postgres - PostgreSQL 13.1. My jsonb column value looks like this:

'{
    "usertype": [
        {
            "type": "staff",
            "status": "active",
            "permissions": {
                "1": "add user",
                "2": "add account"
            }
        },
        {
            "type": "customer",
            "status": "suspended",
            "permissions": {
                "1": "add",
                "2": "edit",
                "3": "view",
                "4": "all"
            }
        }
    ]
}'

I would like to produce a table style output where each permission item i shown as a column. It should show the value if not null else it will be NULL.

 type     | status    | perm1   | perm2      | perm3 | perm4 | perm5 | perm6
----------+-----------+---------+------------+-------+-------+-------+-------
 staff    | active    | adduser | addaccount | null  | null  | null  | null
 customer | suspended | add     | edit       | view  | all   | null  | null

In other words, I would like a way to find out the max permissions count and show that many column in the select query.

Answer 1

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

1. Determine the list or result columns.
2. 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.