JOIN on bigint[] doesn't return the field with array of populated objects

user_2891356 0
Viewed 57

Edit

I ended up doing the following:

SELECT
    u.*,
    temp.json_data as roles
FROM my_schema.user as u
CROSS JOIN LATERAL (
    SELECT jsonb_agg(
        json_build_object(
            'id', r.id,
            'role_name', r.role_name
        )
    ) as json_data
    FROM UNNEST(u.roles) as fkl(id)
    INNER JOIN my_schema.role as r
        ON r.id = fkl.id
) as temp
WHERE u.user_name = 'John'

I have a couple of tables that look like this,

CREATE TABLE IF NOT EXISTS my_schema.role (
    id serial PRIMARY KEY,
    role_name VARCHAR (255) UNIQUE NOT NULL
);

CREATE TABLE IF NOT EXISTS my_schema.user (
    id serial PRIMARY KEY,
    user_name VARCHAR (255) UNIQUE NOT NULL,
    roles BIGINT[] NOT NULL
);

Suppose I have the following records in each:

-- user
id       user_name       roles
 1       'Mike'          [1, 3]
 2       'John'          [3]

-- role
id       role_name
 1       admin
 2       super
 3       common

I want to join these two tables, here's the query:

SELECT
    u.*
FROM my_schema.user u
LEFT JOIN my_schema.role r
    ON r.id = ANY(u.roles)
WHERE u.user_name = 'John'

But this returns the following:

{
  id: 2,
  user_name: 'John',
  roles: [ '3' ]
}

Why does this not populate the roles field? I'd expect the following output:

{
  id: 2,
  user_name: 'John',
  roles: [
    {
      id: 3,
      role_name: 'common'
    }
  ]
}

What am I doing wrong?

postgresql sql
1 Answers

You can simplify your query.

Just join role table on existence of role.id in unnested user.roles and aggregate it values in json, like this

SELECT
    u.*,
    jsonb_agg(
        json_build_object(
            'id', r.id,
            'role_name', r.role_name
        )
    ) as json_data
FROM my_schema.user as u
JOIN my_schema.role as r ON r.id IN (
    SELECT
        UNNEST(roles)
    FROM my_schema.user ur 
    WHERE ur.id = u.id
)
WHERE u.user_name = 'John'
GROUP BY u.id

Or even without subquery, like this

SELECT
    u.*,
    JSONB_AGG(
        JSON_BUILD_OBJECT(
            'id', r.id,
            'role_name', r.role_name
        )
    ) AS json_data
FROM my_schema.user u
JOIN my_schema.role r ON ARRAY[r.id::bigint] @> u.roles
WHERE u.user_name = 'John'
GROUP BY u.id

By the way, why are you using BIGINT array to store roles values in user table, if role.id is of a serial type, not a bigserial? If you make roles to be of a type INT[], than the casting r.id::bigint in JOIN condition could be removed.

user_58768030
Related