在postgres jsonb数组中查找对象的位置 [英] Find position of object in postgres jsonb array

问题描述

我在表的jsonb value列中有一个看起来像这样的对象数组:

I have an array of objects that looks like this within the jsonb value column of a table:

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

我的目标是根据其ID和版本从此数组中删除某些对象，如下所示:

I'm aiming to remove certain objects from this array based on their id and version like so:

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

但是，1不应进行硬编码，而应等于对象在数组中的位置，该位置与我要查找的ID和版本(在本例中为"id": "cd92e346-6b04-3456-050a-5eeb0bddd027", "version": 3)相匹配.

However, the 1 should not be hard-coded, but rather should equal the position of the object within the array which matches the id and version I'm looking for (in this case "id": "cd92e346-6b04-3456-050a-5eeb0bddd027", "version": 3).

如何确定该数组的位置并将其传递到我的硬编码1当前所在的位置?

How would I go about determining this array position and passing it into the spot my hard-coded 1 currently holds?

推荐答案

示例数据:

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 确定数组给定元素的位置:

You can use jsonb_array_elements() with ordinality to determine the array position of a given element:

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从数组中删除对象:

Use position to remove the object from the array:

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)

这篇关于在postgres jsonb数组中查找对象的位置的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！