文本数组值PostgreSQL的查询 [英] 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 ...

我想找到具有价值5作为 IDS 列。

我试图通过使用字符串数组功能，并与翻译 [符号来实现这一目标C>功能，但无法找到一种方式。

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.

推荐答案

您可以这样做：的 http://www.sqlfiddle.com/#!1/5c148/12

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

输出：

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

---

您也可以使用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);

要查看原始元素：

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);

输出：

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

---

PostgreSQL的DDL是原子的，如果它不是晚了还没有在你的项目，只是构建您的stringly类型的数组到一个真正的数组：的 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;

查询：

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

输出：

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

您还可以使用含有运营商： http://www.sqlfiddle.com/# ！1 / 6e18c / 6

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

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

我preFER的＆放大器;＆安培; 语法，虽然，它直接蕴含交集。它反映了你检测，如果有两套（阵列是一组）

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/静态/功能-array.html

这篇关于文本数组值PostgreSQL的查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！