使用 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 ordinal 找到搜索元素的索引(注意，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 条件和可能受影响的行数较少)您可能也对 原子更新 .. 在 Postgres 中选择.

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屋！