How to query array which is present as string in postgres?

Question

I have a table called table with column called column with datatype text with values like '["1","2"]'.

I need to get all records which has "1" as one of the element.

select * 
from table 
where column.....?

How should the where clause be?

no... the number can range upto 300. I dont want results with 10,100,101,201.... — Surya
– Surya, Commented Sep 19, 2019 at 10:28
Oh really? so converting the string to array type and then checking with contains operator is an overkill huh? — Surya
– Surya, Commented Sep 19, 2019 at 10:31
@404 yes you are right. The dataype is text have updated the question. — Surya
– Surya, Commented Sep 19, 2019 at 10:34

jarlh · Accepted Answer · 2019-09-20 12:20:57Z

1

Simply use LIKE. Keep the double quotes to pass 1, but avoid other numbers containing that digit.

select * 
from table 
where column like '%"1"%'

answered Sep 20, 2019 at 12:20

jarlh

44.9k8 gold badges52 silver badges68 bronze badges

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

2 Comments

Surya Over a year ago

Hey can you also post in any postgres doc that says about double quotes. It will be helpful

jarlh Over a year ago

That's just how LIKE works. The % wild-card stands for any sequence of zero or more characters. In your case look for something that has zero or some arbitrary characters, then "1", and then zero or some arbitrary characters. postgresqltutorial.com/postgresql-like

roman · Accepted Answer · 2019-09-19 10:49:52Z

1

I think you can use ? operator on jsonb type:

select *
from (
    select '["1","2"]' union all
    select '["0"]'
) as a(data)
where
    a.data::jsonb ? '1'

In general, I'd consider storing your data as jsonb instead of string.

db<>fiddle example

edited Sep 19, 2019 at 10:49

answered Sep 19, 2019 at 10:32

roman

118k30 gold badges205 silver badges209 bronze badges

Collectives™ on Stack Overflow

How to query array which is present as string in postgres?

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related