在 PostgreSQL 9.4 中更新 json 数组的某些数组元素 [英] Update certain array elements of a json array in PostgreSQL 9.4

问题描述

我有一张这样的桌子；

CREATE TABLE test (
  id BIGSERIAL PRIMARY KEY,
  data JSONB
);

INSERT INTO test(data) VALUES('[1,2,"a",4,"8",6]'); -- id = 1
INSERT INTO test(data) VALUES('[1,2,"b",4,"7",6]'); -- id = 2

如何在没有 PL/* 的情况下将元素 data->1 和 data->3 更新为其他内容?

How to update element data->1 and data->3 into something else without PL/*?

推荐答案

您不能直接操作 json/jsonb 类型的选定元素.Postgres 9.4 中仍然缺少此功能(请参阅@Craig 的评论).您必须执行 3 个步骤:

You cannot manipulate selected elements of a json / jsonb type directly. Functionality for that is still missing in Postgres 9.4 (see @Craig's comment). You have to do 3 steps:

1. 取消嵌套/分解 JSON 值.
2. 操作选定的元素.
3. 再次聚合/组合值.

用给定的 id = 1 替换 json 数组的第三个元素(data->3)pg 9.4中的(新)值('<new_value>'):

To replace the 3rd element of the json array (data->3) in the row with id = 1 with a given (new) value ('<new_value>') in pg 9.4:

UPDATE test t
SET    data = t2.data
FROM  (
   SELECT id, array_to_json(
                 array_agg(CASE WHEN rn = 1 THEN '<new_value>' ELSE elem END))
              ) AS data
   FROM   test t2
        , json_array_elements_text(t2.data) WITH ORDINALITY x(elem, rn)         
   WHERE  id = 1
   GROUP  BY 1
   ) t2
WHERE  t.id = t2.id
AND    t.data <> t2.data; -- avoid empty updates

关于json_array_elements_text():

• 如何将json数组转成postgres数组?

关于WITH ORDINALITY:

• 带有元素编号的PostgreSQL unnest()

这篇关于在 PostgreSQL 9.4 中更新 json 数组的某些数组元素的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！