How do I check if a string is included in an array?

Question

In my PostgreSQL database (version: 11.4) I have a table called table_1 which has only one column. The data type of this column is string array (_varchar).

| dependencies   |
|----------------|
|{\1}            | 
|{\1,\1\2}       |
|{\1,\1\2,\1\2\3}|

Also, I have a table called table_2 which has such structure:

| employee       | dependence |
|----------------|------------|
| Alex           | \1         |
| Mark           | \1         |
| Lily           | \1\2       |
| Grace          | \1\2       |
| Evie           | \1\2       |
| Bob            | \1\2\3     |
| Mark           | \1\2       |

How do I check if a string is included in an array? In my case, I tried to check if the dependence column value from table_2 exists in arrays of the dependencies column of table_1.

In other words, I'm trying to get a such result:

| dependencies   | total |
|----------------|-------|
|{\1}            | 2     | 
|{\1,\1\2}       | 6     |
|{\1,\1\2,\1\2\3}| 7     |

SQL request which I tried:

select
    table_1.dependencies,
    calculation.total
from
    table_1
join lateral (
    select
        count(*) as total
    from
        table_2
    where
        table_2.dependence in table_1.dependencies /* ? */
) calculation on 1 = 1

Naor Tedgi · Accepted Answer · 2020-02-26 19:58:31Z

try this with field TEXT[]

select dependencies ,count(*) from table_1 join table_2
on dependence  = ANY( dependencies)
group by 1

table_1

create table table_1
(
    dependencies text[]
);
insert into public.table_1 (dependencies) values ('{1}');
insert into public.table_1 (dependencies) values ('{1,12}');
insert into public.table_1 (dependencies) values ('{1,12,123}');

table_2

create table if not exists table_2
    (
        employee varchar,
        dependence text
    );
insert into public.table_2 (employee, dependence) values ('Mark', 12);
insert into public.table_2 (employee, dependence) values ('Lily', 12);
insert into public.table_2 (employee, dependence) values ('Evie', 12);
insert into public.table_2 (employee, dependence) values ('Alex', 1);
insert into public.table_2 (employee, dependence) values ('Mark', 1);
insert into public.table_2 (employee, dependence) values ('Grace', 12);
insert into public.table_2 (employee, dependence) values ('Bob', 123);

Collectives™ on Stack Overflow

How do I check if a string is included in an array?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related