Check if string is the one of array-CodePudding

I want to add a where condition if the item exist in array, how to make this conditional statement?

SELECT 
        *
FROM
        table
WHERE
        id = 'XX'
AND
        tag in {'one', 'two', 'three'};

CodePudding user response：

You're almost there! Just replace { with (.

Let's test by creating table first:

create table test(
    id varchar(2),
    tag varchar(10)
);

Insert some values:

insert into test(id, tag) values ('XX', 'one'), ('YY', 'two'), ('XX', 'three'), ('XX', 'four');

Execute your query:

SELECT 
        *
FROM
        test
WHERE
        id = 'XX'
AND
        tag in ('one', 'two', 'three');

You should see the result as below:

id|tag  |
-- ----- 
XX|one  |
XX|three|

CodePudding user response：

There are various ways, depending on what exactly you want to do and what type is the column you want to query: string, integer, enum, json...

Basically I have only used in (...) notation, but I found way with using any as well.

If your column (myColumn) is an array, you can use this

SELECT * FROM myTable
WHERE 'myStringValue/enum' = ANY (myColumn)

But if your column (myColumn) is not an array (scalar), you can use this

SELECT * FROM myTable
WHERE myColumn IN (...values)