CODEWITHSUNDEEP
sqlarrayspostgresql
Casca Rrabias
Casca Rrabias

Reputation: 53

PostgreSQL query on text array value

I have a table where one column has an array - but stored in a text format:

mytable

id  ids
--  -------
1   '[3,4]'
2   '[3,5]'
3   '[3]'
etc ...

I want to find all records that have the value 5 as an array element in the ids column.

I was trying to achieve this by using the "string to array" function and removing the [ symbols with the translate function, but couldn't find a way.

Upvotes: 5

Views: 12518

Answers (2)

Michael Buen
Michael Buen

Reputation: 39393

You can do this: http://www.sqlfiddle.com/#!1/5c148/12

select *
from tbl
where translate(ids, '[]','{}')::int[] && array[5];

Output:

| ID |   IDS |
--------------
|  2 | [3,5] |

You can also use bool_or: http://www.sqlfiddle.com/#!1/5c148/11

with a as
(
  select id, unnest(translate(ids, '[]','{}')::int[]) as elem
  from tbl
)
select id
from a
group by id
having bool_or(elem = 5);

To see the original elements:

with a as
(
  select id, unnest(translate(ids, '[]','{}')::int[]) as elem
  from tbl
)
select id, '[' || array_to_string(array_agg(elem), ',') || ']' as ids
from a
group by id
having bool_or(elem = 5);

Output:

| ID |   IDS |
--------------
|  2 | [3,5] |

Postgresql DDL is atomic, if it's not late yet in your project, just structure your stringly-typed array to a real array: http://www.sqlfiddle.com/#!1/6e18c/2

alter table tbl
add column id_array int[];

update tbl set id_array = translate(ids,'[]','{}')::int[];

alter table tbl drop column ids;

Query:

select *
from tbl
where id_array && array[5]

Output:

| ID | ID_ARRAY |
-----------------
|  2 |      3,5 |

You can also use contains operator: http://www.sqlfiddle.com/#!1/6e18c/6

select *
from tbl
where id_array @> array[5];

I prefer the && syntax though, it directly connotes intersection. It reflects that you are detecting if there's an intersection between two sets(array is a set)

http://www.postgresql.org/docs/8.2/static/functions-array.html

Upvotes: 5

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656724

If you store the string representation of your arrays slightly differently, you can cast to array of integer directly:

INSERT INTO mytable
VALUES 
 (1, '{3,4}')
,(2, '{3,5}')
,(3, '{3}');

SELECT id, ids::int[]
FROM   mytable;

Else, you have to put in one more step:

SELECT (translate(ids, '[]','{}'))::int[]
FROM   mytable

I would consider making the column an array type to begin with.

Either way, you can find your row like this:

SELECT id, ids 
FROM  (
    SELECT id, ids, unnest(ids::int[]) AS elem
    FROM   mytable
    ) x
WHERE  elem = 5

Upvotes: 3

Related Questions