0

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

enter image description here

Progress enter image description here all the columns are coming out as null

bihire boris
  • 1,530
  • 3
  • 19
  • 44

1 Answers1

1

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'
)
Edouard
  • 6,577
  • 1
  • 9
  • 20
  • Add single quotes to JSON attribute names, i.e. `j->>'role'`. Better use `ON (j->>'role')::integer = r.id` then `ON j->>'role' = r.id::text` in order to not defeat the index - probably primary key - on `role(id)`. – Stefanov.sm Dec 03 '21 at 09:43
  • @Stefanov.sm Yes sure, thanks – Edouard Dec 03 '21 at 09:48
  • I am getting this error back `function jsonb_array_elements(jsonb[]) does not exist` which I am guessing it has to do with how I defined the column `associations: { allowNull: true, type: DataTypes.ARRAY(DataTypes.JSONB), defaultValue: [] }` – bihire boris Dec 03 '21 at 10:07
  • 2
    The `jsonb_array_elements` function applies to the `jsonb` data type, whereas your column association is of type `jsonb[]`. So in your case, just replace `jsonb_array_elements()` by `unnest()`, answer updated accordingly. – Edouard Dec 03 '21 at 10:14
  • @bihireboris Worth mentioning that you should probably avoid `jsonb[]`. Instead, using `jsonb` as a column type and just store a JSON array in it – Bergi Dec 03 '21 at 10:29
  • why though?If I may ask @Bergi – bihire boris Dec 03 '21 at 10:33
  • now getting this error back `invalid input syntax for type integer: \"role\"` I added an Image in the questions to describe better what's in the tables @Edouard – bihire boris Dec 03 '21 at 10:42
  • 2
    @bihireboris see https://stackoverflow.com/a/62917486/1048572 https://stackoverflow.com/q/56624552/1048572 https://stackoverflow.com/q/66141481/1048572 – Bergi Dec 03 '21 at 10:43
  • any update on this?all I tried failed including this https://stackoverflow.com/a/54337777/10238136 – bihire boris Dec 03 '21 at 16:49
  • I've added brakets before `:: integer`. Can you first execute in your postgres client the query SELECT * FROM users ... WHERE id = 4 while removing the backslashes ? If the sql query provides the expected result, then you will have to embed it in your sequelize query while finding the right way to escape the ' characters. – Edouard Dec 03 '21 at 17:04
  • So I am getting values like in the image added in the question(check it out) but all the values of the joined table are null can you spot why @Edouard – bihire boris Dec 03 '21 at 18:12
  • Basically it means there is no row in table role which matches with `j->>'role'`. Difficult to tell you more based on an image. Can you create the tables users and role in [dbfiddle](https://dbfiddle.uk/?rdbms=postgres_14) and insert a sample of data in both tables so that to better analyse the situation ? – Edouard Dec 03 '21 at 21:13
  • https://dbfiddle.uk/?rdbms=postgres_14&fiddle=98e2da4c45fa908163b08f4c3daf3b8e will this do? – bihire boris Dec 04 '21 at 19:19
  • yes perfect. I've updated your [dbfidle](https://dbfiddle.uk/?rdbms=postgres_14&fiddle=2a6462b4c9f0f3728957311ffd707ee6). Look at the result of the query. – Edouard Dec 04 '21 at 20:11
  • Thanks for the patience fam, It works, I'll invest more time reading about sql +1 :) – bihire boris Dec 04 '21 at 21:02