PostgreSQL select * where column contains array value

Question

I have a postgresql database like this:

users
id     name     companyrestrictions
1      Bill     [3, 4]
2      Fred     [5, 6]

And then a variable for each company 3 in this case

So I wrote a query like this:

SELECT * FROM users WHERE 3 = ANY(users.companyrestrictions)

But I'm getting the following error: op ANY/ALL (array) requires array on right side

The company restrictions is of type jsonb

What am I doing wrong?

As the error says: you can't use an array operator on a JSONB column. There is no equivalent function to test for elements inside an array inside a JSONB column. If all you want to store is an array, then why don't you use an array? — user330315
– user330315, Commented Nov 11, 2017 at 15:59
I don't see an array datatype for Postgres, is there one? What is it called? — Jordash
– Jordash, Commented Nov 11, 2017 at 16:00

Vao Tsun · Accepted Answer · 2017-11-11 16:50:47Z

3

try <@ includes operator:

<@ Are the left JSON path/value entries contained at the top level within the right JSON value?

SELECT * FROM users WHERE '3' <@ users.companyrestrictions

ANY only works for arrays

edited Nov 11, 2017 at 16:50

answered Nov 11, 2017 at 16:00

Vao Tsun

52.4k13 gold badges114 silver badges149 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Jordash Over a year ago

That returned this error: operator does not exist: integer <@ jsonb

Jordash Over a year ago

You're right, I didn't notice you turned the 3 into a string, it works now, will this work even if you have an array like this [3, 33, 333] ?

user330315 Over a year ago

@Jordash: yes, it would still work. But be aware that this only works because your JSON top-level element is an array. It would not work with a JSON like {"foo": [3, 5, 6]}

Jordash Over a year ago

That’s great, yeah I️ know how to work with sub level items

Vao Tsun Over a year ago

Thank you for comments. where '5' <@ (b->'foo')::jsonb; can be used to turn not top elements to top for the path

Thaw · Accepted Answer · 2017-11-11 16:16:38Z

It's probably better to store the company restriction in another table. That's exactly what RDBMS is for.

If you really need to use JSON, you'll probably have to parse it. There may be some JSON functions available in postgres, but it seems like a hassle for an array and you won't be able to query the data using SQL.

CREATE TABLE t_user (
    id   SERIAL                 PRIMARY KEY NOT NULL,
    name CHARACTER VARYING(256) NOT NULL
);

CREATE UNIQUE INDEX t_user_idx ON t_users(name);

CREATE TABLE t_company (
    id   SERIAL                 PRIMARY KEY NOT NULL,
    name CHARACTER VARYING(256) NOT NULL
);

CREATE UNIQUE INDEX t_company_idx ON t_company(name);

CREATE TABLE t_company_restriction (
    id         SERIAL  PRIMARY KEY NOT NULL,
    id_company integer NOT NULL REFERENCES t_company(id) ON DELETE CASCADE,
    id_user    integer NOT NULL REFERENCES t_user(id) ON DELETE CASCADE
);

CREATE UNIQUE INDEX t_company_restriction_idx ON t_company_restriction(id_company, id_user);

INSERT INTO t_user(name) VALUES ('Bill');
INSERT INTO t_user(name) VALUES ('Fred');

INSERT INTO t_company (name) VALUES ('Company 1');
INSERT INTO t_company (name) VALUES ('Company 2');
INSERT INTO t_company (name) VALUES ('Company 3');
INSERT INTO t_company (name) VALUES ('Company 4');

INSERT INTO t_company_restriction (id_user, id_company)
SELECT u.id, c.id FROM t_user u, t_company c
WHERE u.name = 'Bill' AND c.name = 'Company 1';

INSERT INTO t_company_restriction (id_user, id_company)
SELECT u.id, c.id FROM t_user u, t_company c
WHERE u.name = 'Bill' AND c.name = 'Company 2';

INSERT INTO t_company_restriction (id_user, id_company)
SELECT u.id, c.id FROM t_user u, t_company c
WHERE u.name = 'Fred' AND c.name = 'Company 3';

INSERT INTO t_company_restriction (id_user, id_company)
SELECT u.id, c.id FROM t_user u, t_company c
WHERE u.name = 'Fred' AND c.name = 'Company 4';

SELECT u.name
FROM   t_user u, t_company c, t_company_restriction cr
WHERE  c.name = 'Company 1'
AND    c.id = cr.id_company
AND    u.id = cr.id_user;

Collectives™ on Stack Overflow

PostgreSQL select * where column contains array value

2 Answers 2

5 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related