SQL select return keyed object from joins

user_1023216 0
Viewed 62

I have a working SQL query (Postgres 9.6) that returns the information I need, but it's not quite shaped how I want.

SELECT ARRAY(
  SELECT
    row_to_json(a.*)
  FROM (
    SELECT
      proj.slug,
      perm.name AS role
    FROM "Projects" proj
    INNER JOIN "Permissions" perm
    ON perm.project_id = proj.id
    INNER JOIN "UserPermissions" up
    ON up.permission_id = perm.id
    WHERE up.user_id = 1
    ORDER BY lower(proj.name)
  ) a
) as permissions

With this query, the permissions are in an array that looks like

[
  {
    slug: 'abcdef',  
    role: 'owner' 
  },
  { 
    slug: 'abcdef', 
    role: 'admin' 
  },
  {
    slug: 'ghijkl',
    role: 'member'
  }
]

I would like to get the permissions as an object instead, combining the projects together by slug:

{
  'abcdef': {
    slug: 'abcdef',
    roles: ['owner', 'admin']
  },
  'ghijkl': {
    slug: 'ghijkl',
    roles: ['member']
  }
}

Currently I'm doing this after the query with application code.

Is there a way to return this shape with a SQL query directly?

where-clause postgresql arrays sql json
1 Answers

I think you want two levels of aggregation. First, gather the roles associated to each slug in a json array (using JSON_AGG()), then generate the target json object, using JSON_OBJECT_AGG():

SELECT JSON_OBJECT_AGG(slug, JSON_BUILD_OBJECT('slug', slug, 'roles', roles)) permissions
from (
    SELECT pr.slug, json_agg(pe.name) as roles
    FROM "Projects" pr
    INNER JOIN "Permissions" pe ON pe.project_id = pr.id
    INNER JOIN "UserPermissions" up ON up.permission_id = pe.id
    WHERE up.user_id = 1
    GROUP BY pr.slug
) t
user_106767160
Related