使用jsonb_set()更新特定的jsonb数组值 [英] Using jsonb_set() for updating specific jsonb array value
问题描述
当前，我正在使用PostgreSQL 9.5，并尝试更新jsonb字段数组中的值.但是我无法获得所选值的索引
Currently I am working with PostgreSQL 9.5 and try to update a value inside an array of a jsonb field. But I am unable to get the index of the selected value
我的桌子看起来像这样:
My table just looks like this:
CREATE TABLE samples (
id serial,
sample jsonb
);
我的JSON看起来像这样:
My JSON looks like this:
{"result": [
{"8410": "ABNDAT", "8411": "Abnahmedatum"},
{"8410": "ABNZIT", "8411": "Abnahmezeit"},
{"8410": "FERR_R", "8411": "Ferritin"}
]}
我的SELECT语句获得正确的值有效:
My SELECT statement to get the correct value works:
SELECT
id, value
FROM
samples s, jsonb_array_elements(s.sample#>'{result}') r
WHERE
s.id = 26 and r->>'8410' = 'FERR_R';
导致:
id | value
----------------------------------------------
26 | {"8410": "FERR_R", "8411": "Ferritin"}
好的，这就是我想要的.现在，我想使用以下UPDATE语句执行更新，以在其中添加新元素"ratingtext". (如果还不存在):
Ok, this is what I wanted. Now I want to execute an update using the following UPDATE statement to add a new element "ratingtext" (if not already there):
UPDATE
samples s
SET
sample = jsonb_set(sample,
'{result,2,ratingtext}',
'"Some individual text"'::jsonb,
true)
WHERE
s.id = 26;
执行UPDATE语句后，我的数据如下所示(也是正确的):
After executing the UPDATE statement, my data looks like this (also correct):
{"result": [
{"8410": "ABNDAT", "8411": "Abnahmedatum"},
{"8410": "ABNZIT", "8411": "Abnahmezeit"},
{"8410": "FERR_R", "8411": "Ferritin", "ratingtext": "Some individual text"}
]}
到目前为止很好，但是我手动搜索了索引值2以在JSON数组中获取正确的元素.如果要更改顺序，将无法使用.
So far so good, but I manually searched the index value of 2 to get the right element inside the JSON array. If the order will be changed, this won't work.
所以我的问题:
有没有一种方法来获取所选JSON数组元素的索引，并将SELECT语句和UPDATE语句组合为一个?
Is there a way to get the index of the selected JSON array element and combine the SELECT statement and the UPDATE statement into one?
就像:
UPDATE
samples s
SET
sample = jsonb_set(sample,
'{result,' || INDEX OF ELEMENT || ',ratingtext}',
'"Some individual text"'::jsonb,
true)
WHERE
s.id = 26;
在准备该语句之前，已知道 samples.id 和"8410&" 的值.
The values of samples.id and "8410" are known before preparing the statement.
还是目前无法实现?
推荐答案
您可以使用jsonb_array_elements() with ordinality查找搜索到的元素的索引(注意，ordinality从1开始，而json数组的第一个索引为0):
You can find an index of a searched element using jsonb_array_elements() with ordinality (note, ordinality starts from 1 while the first index of json array is 0):
select
pos- 1 as elem_index
from
samples,
jsonb_array_elements(sample->'result') with ordinality arr(elem, pos)
where
id = 26 and
elem->>'8410' = 'FERR_R';
elem_index
------------
2
(1 row)
使用上面的查询根据元素的索引来更新元素(请注意，jsonb_set()的第二个参数是文本数组):
Use the above query to update the element based on its index (note that the second argument of jsonb_set() is a text array):
update
samples
set
sample =
jsonb_set(
sample,
array['result', elem_index::text, 'ratingtext'],
'"some individual text"'::jsonb,
true)
from (
select
pos- 1 as elem_index
from
samples,
jsonb_array_elements(sample->'result') with ordinality arr(elem, pos)
where
id = 26 and
elem->>'8410' = 'FERR_R'
) sub
where
id = 26;
结果:
select id, jsonb_pretty(sample)
from samples;
id | jsonb_pretty
----+--------------------------------------------------
26 | { +
| "result": [ +
| { +
| "8410": "ABNDAT", +
| "8411": "Abnahmedatum" +
| }, +
| { +
| "8410": "ABNZIT", +
| "8411": "Abnahmezeit" +
| }, +
| { +
| "8410": "FERR_R", +
| "8411": "Ferritin", +
| "ratingtext": "Some individual text"+
| } +
| ] +
| }
(1 row)
jsonb_set()中的最后一个参数应为true，以在其键尚不存在时强制添加新值.但是可以跳过它，因为它的默认值为true.
The last argument in jsonb_set() should be true to force adding a new value if its key does not exist yet. It may be skipped however as its default value is true.
尽管并发问题似乎不太可能(由于限制性WHERE条件和受影响的行的数量可能很小)，您可能也对
Though concurrency issues seem to be unlikely (due to the restrictive WHERE condition and a potentially small number of affected rows) you may be also interested in Atomic UPDATE .. SELECT in Postgres.
这篇关于使用jsonb_set()更新特定的jsonb数组值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
