使用jsonb_set更新特定jsonb数组值的PostgreSQL 9.5 [英] postgresql 9.5 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
我的桌子看起来像:
CREATE TABLE samples (
id serial,
sample jsonb
);
我的JSON如下:
{"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 execute 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数组值的PostgreSQL 9.5的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!