在Postgres的jsonb数组中查找对象位置

Question

我有一个对象数组，它位于表的jsonb value列中，看起来像这样：

"west": [
  {"id": "aa92f346-7a93-4443-949b-4eab0badd983", "version": 1},
  {"id": "cd92e346-6b04-3456-050a-5eeb0bddd027", "version": 3}
]

我希望能够根据id和版本从该数组中删除特定的对象，方法如下：

SELECT value::jsonb #- '{west, 1}' FROM game.settings;

然而，1 不应该是硬编码的，而是应该等于数组中与我正在寻找的 id 和 version 相匹配的对象的位置（在这种情况下为 "id": "cd92e346-6b04-3456-050a-5eeb0bddd027", "version": 3）。

我该如何确定这个数组位置并将其传递到我的硬编码 1 所占据的位置？

- MattDionis

Answer 1

示例数据：

create temp table settings as
select 
    '{"west": [
      {"id": "aa92f346-7a93-4443-949b-4eab0badd983", "version": 1},
      {"id": "cd92e346-6b04-3456-050a-5eeb0bddd027", "version": 3}
    ]}'::jsonb as value;

你可以使用jsonb_array_elements() with ordinality来确定给定元素在数组中的位置：

select elem, position
from settings, jsonb_array_elements(value->'west') with ordinality arr(elem, position)
where elem->>'id' = 'cd92e346-6b04-3456-050a-5eeb0bddd027';

                             elem                             | position 
--------------------------------------------------------------+----------
 {"id": "cd92e346-6b04-3456-050a-5eeb0bddd027", "version": 3} |        2
(1 row)

使用position从数组中移除对象：

select value #- array['west', (position- 1)::text] new_value
from settings, jsonb_array_elements(value->'west') with ordinality arr(elem, position)
where elem->>'id' = 'cd92e346-6b04-3456-050a-5eeb0bddd027';

                                new_value                                 
--------------------------------------------------------------------------
 {"west": [{"id": "aa92f346-7a93-4443-949b-4eab0badd983", "version": 1}]}
(1 row)

- klin

---

谢谢，我正在寻找该对象在数组中的索引。 - MattDionis

我不小心粗心地阅读了问题，很抱歉。我希望修改后的答案现在反映了真正的问题。 - klin