Posgresql - jsonb object reduce without a subquery

Question

I have a jsonb column with permissions of users like this (this is a value of one record):

{
   "user_1":{
      "permissions":[
         "edit",
         "view"
      ]
   },
   "user_2":{
      "permissions":[

      ]
   },
   "user_3":{
      "permissions":[
         "view"
      ]
   }
}

What I want to achieve is to transform this jsonb object into a jsonb array of objects like this:

[
   {
      "login":"user_1",
      "permissions":[
         "edit",
         "view"
      ]
   },
   {
      "login":"user_2",
      "permissions":[

      ]
   },
   {
      "login":"user_3",
      "permissions":[
         "view"
      ]
   }
]

Just "move" login key to a separate field and index it as an array.

The question is:
Is it possible it do it in PostgreSQL (version 10.x) without a subquery ?

This is my version with a subquery:

SELECT jsonb_agg(jsonb_build_object('login', tbl.key, 'permissions', tbl.value->'permissions')), id FROM (
    SELECT (jsonb_each(permissions_tbl.permissions_obj)).*, id
    FROM (
        SELECT '{"user_1": {"permissions": ["edit", "view"]}, "user_2": {"permissions": []}, "user_3": {"permissions": ["view"]}}'::jsonb permissions_obj, 123 id
    ) as permissions_tbl

) as tbl GROUP BY id;

I used jsonb_each, jsonb_build_object and jsonb_agg. id is releated to permissions_obj containing multiple users and permissions. Any advice to impove the query is really welcome.

S-Man S-Man · Accepted Answer · 2019-11-25T15:14:33

Click: step-by-step demo:db<>fiddle

SELECT
    jsonb_agg(                                  -- 4
        jsonb_build_object('login', key)        -- 2            
        || value                                -- 3
    )
FROM
    mytable,
    jsonb_each(mydata)                          -- 1

Expand the JSON object into one row per element. That creates two new columns: key (which are your logins) and value (which are your permissions objects)
Create a new JSON object for the logins
Add the permissions objects to the new JSON objects
Reaggregate them into a JSON array

Posgresql - jsonb object reduce without a subquery

1 Answers