How to perfom adequate operations on JSONB PostgresSQL-CodePudding

Lets say I have

sequelize.query('SELECT associations FROM users WHERE id = :id')

associations is a JSONB ARRAY column

the output look like so

[
        {
            "role": 2,
            "shop_id": 1,
            "admin_id": 1,
            "manager_id": null
        }
    ]

I'd like to loop through the array and search for those associations using those ids

I'd like to perfom that whole thing in the same query.

I have a role table, shop table, users table

Progress all the columns are coming out as null

CodePudding user response：

If association is a column of type jsonb[], then use unnest(association) in order to expand the first level of elements.

Then you can try something like this assuming that all the id are of type integer :

sequelize.query('
 SELECT *
   FROM users
  CROSS JOIN LATERAL unnest(associations) AS j
   LEFT JOIN role AS r
     ON (j->>\'role\') :: integer = r.id
   LEFT JOIN shop AS s
     ON (j->>\'shop_id\') :: integer = s.id
   LEFT JOIN users AS a
     ON (j->>\'admin_id\') :: integer = a.id
   LEFT JOIN users AS m
     ON (j->>\'manager_id\') :: integer = m.id
  WHERE id = :id'
)