Return JSON from Postgres with JSON_BUILD_OBJ - Postgresql

foo_bar_zing
March 22, 2023
145 views
3 votes
3 Answers

I have a Postgres DB setup, and I’m trying to return a certain data shape back from a query.

My data looks like this:

CREATE TABLE resource_actions(
resource TEXT NOT NULL,
actions TEXT NOT NULL,
entity TEXT NOT NULL,
UNIQUE(resource, actions, entity)
);


INSERT INTO resource_actions (resource, actions, entity) VALUES
('reading-list', 'read', 'Citizen'),
('reading-list', 'read', 'Employee'),
('books', 'read', 'Employee'),
('titles', 'update', 'Citizen'),
('titles', 'read', 'Citizen'),
('titles', 'update', 'Employee'),
('titles', 'read', 'Employee'),
('authors', 'update', 'Employee'),
('authors', 'read', 'Citizen'),
('reviews', 'read', 'Citizen'),
('reviews', 'read', 'Employee'),
('reviews', 'create', 'Citizen'),
('reviews', 'create', 'Employee'),
('employees', 'read', 'Employee'),
('employees', 'read', 'Boss'),
('employees', 'delete', 'Boss'),
('employees', 'create', 'Boss'),
('employees', 'update', 'Boss'),
('employee-schedule', 'read', 'Boss'),
('employee-schedule', 'delete', 'Boss'),
('employee-schedule', 'create', 'Boss'),
('employee-schedule', 'update', 'Boss');

I want to return data like so:

{
    read:['Citizen':['reading-list', 'titles', 'authors','reviews'], 'Employee':['books','reading-list', 'titles', 'authors'], 'Boss':[]]
    create:['Citizen':['reviews'], 'Employee':['reviews'], 'Boss':['employees','employee-schedule']],
    update: ['Citizen':['titles'], 'Employee':['authors','titles'], 'Boss':['employees','employee-schedule']],
    delete:['Citizen':[], 'Employee':[], 'Boss':['employees','employee-schedule']]
}

I have a SQL query which is close, I just can’t for the life of me figure out what I need to change to get the right shape of the data. The query I have is here:

select actions, 
JSON_AGG(JSON_BUILD_OBJECT(resource, entity)) AS groupedActions
FROM resource_actions 
GROUP BY actions

I have a SQL fiddle showing what I have so far: http://sqlfiddle.com/#!17/93bf5/1/0

It gets me close, but I’m not understanding how to aggregate the data by entity and show an array of the resources they have access to. Any help is appreciated!

Answers

select json_build_object(actions, array_agg(groupedResources)) as jsonResult
from (select actions, json_build_object(entity, array_agg(resource)) as groupedResources
      from resource_actions
      group by actions, entity) tmp
group by actions;

jsonresult
{"create" : [{"Citizen" : ["reviews"]},{"Employee" : ["reviews"]},{"Boss" : ["employees","employee-schedule"]}]}
{"read" : [{"Boss" : ["employees","employee-schedule"]},{"Citizen" : ["reading-list","titles","authors","reviews"]},{"Employee" : ["reading-list","books","titles","reviews","employees"]}]}
{"update" : [{"Boss" : ["employees","employee-schedule"]},{"Citizen" : ["titles"]},{"Employee" : ["titles","authors"]}]}
{"delete" : [{"Boss" : ["employees","employee-schedule"]}]}

DBfiddle demo

All as single JSON:

select json_agg(jsonResult)
from (
select json_build_object(actions, json_agg(groupedResources)) as jsonResult
from (select actions, json_build_object(entity, array_agg(resource)) as groupedResources
      from resource_actions
      group by actions, entity) tmp
group by actions) x;

DBFiddle demo

To aggregate actions with concatenation you can use a custom aggregation

CREATE AGGREGATE json_concat_agg(jsonb) (
  SFUNC = 'jsonb_concat',
  STYPE = jsonb,
  INITCOND = '{}'
);


select json_concat_agg(ga::jsonb)::json
    from (
    select JSON_BUILD_OBJECT(actions, JSON_AGG(ge)) ga
    from (
       select actions, JSON_BUILD_OBJECT(entity, JSON_AGG(resource)) AS ge
       FROM resource_actions 
       GROUP BY actions, entity
    ) t
    group by actions
) t

db<>fiddle

{
"read": [{"Boss": ["employees", "employee-schedule"]}, {"Citizen": ["reading-list", "titles", "authors", "reviews"]}, {"Employee": ["reading-list", "books", "titles", "reviews", "employees"]}], 
"create": [{"Citizen": ["reviews"]}, {"Employee": ["reviews"]}, {"Boss": ["employees", "employee-schedule"]}],
"delete": [{"Boss": ["employees", "employee-schedule"]}], 
"update": [{"Boss": ["employees", "employee-schedule"]}, {"Citizen": ["titles"]}, {"Employee": ["titles", "authors"]}]
}

You can construct objects in subqueries/CTEs, then collect them with jsonb_object_agg(): demo

select jsonb_object_agg(actions,entity_resources) result 
from (select actions,
             jsonb_agg(entity_resources) entity_resources
      from (select actions,
                   jsonb_build_object(entity,jsonb_agg(resource)) entity_resources
            from resource_actions 
            group by actions,
                     entity ) innermost_subquery
      group by actions ) subquery;

Use jsonb if you can: it’s indexable, lighter, faster and more flexible, offering more functions and operators, including jsonpath interaction, at a small processing cost on input.
If you can’t, all jsonb functions above have direct json equivalents they can be swapped out for.

Except for jsonb_pretty() below, where you’ll have to cast json to jsonb. If you want to prettify some text-based json, you can feed it as a string literal – it’ll be detected as unknown and auto-cast to a supported type.

Output through jsonb_pretty():

{
    "read": [
        {
            "Boss": [
                "employees",
                "employee-schedule"
            ]
        },
        {
            "Citizen": [
                "reading-list",
                "titles",
                "authors",
                "reviews"
            ]
        },
        {
            "Employee": [
                "reading-list",
                "books",
                "titles",
                "reviews",
                "employees"
            ]
        }
    ],
    "create": [
        {
            "Citizen": [
                "reviews"
            ]
        },
        {
            "Employee": [
                "reviews"
            ]
        },
        {
            "Boss": [
                "employees",
                "employee-schedule"
            ]
        }
    ],
    "delete": [
        {
            "Boss": [
                "employees",
                "employee-schedule"
            ]
        }
    ],
    "update": [
        {
            "Boss": [
                "employees",
                "employee-schedule"
            ]
        },
        {
            "Citizen": [
                "titles"
            ]
        },
        {
            "Employee": [
                "titles",
                "authors"
            ]
        }
    ]
}

Please signup or login to give your own answer.

Click here to cancel reply.

Return JSON from Postgres with JSON_BUILD_OBJ – Postgresql

Answers