如何在PostgreSQL中获取数组值的索引？

Question

我有一个名为pins的表格，它长这样：

id (int) |      pin_codes (jsonb)
--------------------------------
1        |  [4000, 5000, 6000]
2        |  [8500, 8400, 8600]
3        |  [2700, 2300, 2980]

现在，我想要包含 pin_code 8600 并带有其数组索引的行。输出必须如下所示：

     pin_codes       |  index
------------------------------
[8500, 8500, 8600]   |   2

如果我想要带有pin_code为2700的行，输出结果是：

     pin_codes       |  index
------------------------------
[2700, 2300, 2980]   |   0

到目前为止，我尝试过以下方法：

SELECT pin_codes FROM pins WHERE pin_codes @> '[8600]'

它只返回所需值的行。我不知道如何获取pin_codes数组中值的索引！

任何帮助都将不胜感激。

P.S：

我正在使用PostgreSQL 10

- Hamed Kamrava

---

你真的需要去规范化你的数据。SQL并不适合解析那样的数据。如果你无法解析和去规范化数据，那么你下一个最好的选择就是在应用层中进行子字符串匹配，并确定索引（在那里解析通常要容易得多）。 - D Stanley

你是否被这个数据模型卡住了，还是可以重新设计一下？ - Twelfth

@HamedKamrava - SQL 不太能很好地处理数组，最好将每个值作为单独的行分别存储。一个名为 pins（pin_id，misc_info_columns）的表。一个名为 Pins_info（pin_id，pin_seq，pin_value）的表。这只是一个针对未知数量的针脚设计的例子，它取决于您要用它做什么... SQL 的有趣之处在于通常有20种好的解决方案来解决任何问题，但是找到最适合您用例的最佳解决方案是一个挑战。如果您拥有一致数量的值，则表 pin（pin_id，pin1，pin2，pin3）也可以使用。 - Twelfth

@HamedKamrava - 正如您所看到的，现有的答案都是将数组拆分为SQL更易处理的东西。 - Twelfth

@Twelfth：在Postgres中，_数组_实际上非常强大。然而，将数组放入JSON中会使事情变得复杂。 - user330315

Answer 1

如果您将数组存储为 真正的 数组而不是json，您可以使用 array_position() 找到给定元素的（第一个）索引：

select array_position(array['one', 'two', 'three'], 'two') 

返回2

通过一些文本处理，您可以将JSON数组转换为文本数组：

select array_position(translate(pin_codes::text,'[]','{}')::text[], '8600')
from the_table;

该工具还允许您使用“运算符”

select *
from pins
where '8600' = any(translate(pin_codes::text,'[]','{}')::text[])

使用@>运算符期望该运算符两侧都是数组。您可以使用它同时搜索两个 PIN 码：

select *
from pins
where translate(pin_codes::text,'[]','{}')::text[] @> array['8600','8400']

或者使用重叠运算符&&查找具有多个元素之一的行：

select *
from pins
where translate(pin_codes::text,'[]','{}')::text[] && array['8600','2700']

将返回

id | pin_codes         
---+-------------------
 2 | [8500, 8400, 8600]
 3 | [2700, 2300, 2980]

如果你经常这样做，将 pin_codes 存储为 text[] 而不是 JSON 会更有效率-然后你也可以对该列建立索引以更加高效地进行搜索。

- user330315

---

请注意，array_position 函数仅适用于 PostgreSQL 9.5 及以上版本。 - Ihor Havryliv

Answer 2

使用函数jsonb_array_elements_text()结合with ordinality子句。

with my_table(id, pin_codes) as (
values
    (1, '[4000, 5000, 6000]'::jsonb),
    (2, '[8500, 8400, 8600]'),
    (3, '[2700, 2300, 2980]')
)

select id, pin_codes, ordinality- 1 as index
from my_table, jsonb_array_elements_text(pin_codes) with ordinality
where value::int = 8600;

 id |     pin_codes      | index 
----+--------------------+-------
  2 | [8500, 8400, 8600] |     2
(1 row)

- klin

---

规范化的表肯定会更有效率。然而，我不是一个正统派，认为这取决于具体情况。如果当前使用jsonb的解决方案没有问题，请继续使用它。或者用integer[]替换jsonb。 - klin

针对2020年的读者，这是最佳答案，现代 SQL使用with ordinality。它也很容易重命名，例如：SELECT * FROM UNNEST(x) WITH ORDINALITY t(x_i,i)。 - Peter Krauss

Answer 3

正如之前所指出的，array_position函数仅适用于Postgres 9.5及更高版本。

这里是一个自定义函数，它可以实现相同的功能，源自github上的nathansgreen。

注意：本文中的“Original Answer”翻译成中文为“最初的回答”。

-- The array_position function was added in Postgres 9.5.
-- For older versions, you can get the same behavior with this function.

create function array_position(arr ANYARRAY, elem ANYELEMENT, pos INTEGER default 1) returns INTEGER
language sql
as $BODY$
select row_number::INTEGER
from (
    select unnest, row_number() over ()
    from ( select unnest(arr) ) t0
) t1
    where row_number >= greatest(1, pos)
    and (case when elem is null then unnest is null else unnest = elem end)
limit 1;
$BODY$;

在这种特殊情况下，创建函数后，以下内容对我起作用。

最初的回答：

SELECT 
pin_codes,
array_position(pin_codes, 8600) AS index
FROM pins
WHERE array_position(pin_codes, 8600) IS NOT NULL;

请注意，它只会返回8600的第一个出现的索引，您可以使用pos参数来索引任何您喜欢的出现位置。

原始答案翻译成中文为"最初的回答"。

- hammoire

Answer 4

如果您坚持要存储数组，我会推荐klins的答案。

作为我的评论的替代答案和扩展...不要将SQL数据存储在数组中。提前“规范化”您的数据，SQL将处理得更好。Klin的答案很好，但可能会因为它超出了SQL最擅长的领域而受到性能的影响。

在存储之前，我会先拆分数组。如果知道邮政编码的数量，那么只需拥有表格pin_id、pin1、pin2、pin3、pinetc...就可以了。

如果针脚数目未知，则第一张表格作为存储针脚ID和与该针脚ID相关的任何信息列的针脚，第二张表格作为pin_id、pin_seq、pin_value也是可行的（尽管您可能需要稍后将其旋转以理解数据）。在这种情况下，选择pin_value = 260的pin_seq即可。

- Twelfth

---

数据不是以数组的形式存储，而是以 JSON 的形式存储 - 在 Postgres 中有很大的区别。 - user330315

我应该删除这个答案。 - Twelfth

Answer 5

尝试将字符串展开并按以下方式分配数字：

with dat as
(
  select 1 id, '8700, 5600, 2300' pins
  union all
  select 2 id, '2300, 1700, 1000' pins
)
select dat.*, t.rn as index
from
(
  select id, t.pins, row_number() over (partition by id) rn
  from
  (
    select id, trim(unnest(string_to_array(pins, ','))) pins from dat
  ) t
) t
join dat on dat.id = t.id and t.pins = '2300'

- Radim Bača

Answer 6

简而言之，规范化你的数据结构，否则不要在 SQL 中执行此操作。如果你想得到子数据元素的索引（考虑到当前的数据结构），那么请在你的应用程序代码中完成该操作（获取结果，转换为列表/数组，然后获取索引）。

- hunteke