您的位置: 首页  >  技术问答  >  PostgreSQL如何使用json数组更新列类型json

PostgreSQL如何使用json数组更新列类型json

分类: 技术问答 • 2022-07-21 12:32:22
问题描述：

在PostgreSQL中，我的列类型为json，数据为json数组，例如:

In PostgreSQL, my column type is json , the data is json array like:

[{"attsId": "42a2ce04-52ab-4a3c-8dfb-98c3d14b307d", "planId": 46, "filePath": "fileOperate\\upload", "cfileName": "潜在客户名单 (1).xls", "ufileName": "42a2ce04-52ab-4a3c-8dfb-98c3d14b307d.xls"}, {"attsId": "1adb2f13-00b0-4780-ae76-7a068dc3289c", "planId": 46, "filePath": "fileOperate\\upload", "cfileName": "潜在客户名单.xls", "ufileName": "1adb2f13-00b0-4780-ae76-7a068dc3289c.xls"}, {"attsid": "452f6c62-28df-47c7-8c30-038339f7b223", "planid": 48.0, "filepath": "fileoperate\\upload", "cfilename": "技术市场印花税.xls", "ufilename": "452f6c62-28df-47c7-8c30-038339f7b223.xls"}]

我要更新数组日期之一，例如:

i want update one of the array date like:

UPDATE plan_base set atts->1='{"planId":"71"}' where id= 46;

该怎么做?请帮助我

答

以下是两个帮助器功能，可以实现您的目标(需要PostgreSQL 9.3 +):

Here are two helper functions, to achieve your goal (requires PostgreSQL 9.3+):

可以像UPDATE s一样使用它(仅更新索引，如果它已经存在):

This one can be used like UPDATEs (only updates an index, if it's already exists):

CREATE OR REPLACE FUNCTION "json_array_update_index"(
  "json"            json,
  "index_to_update" INTEGER,
  "value_to_update" anyelement
)
  RETURNS json
  LANGUAGE sql
  IMMUTABLE
  STRICT
AS $function$
SELECT concat('[', string_agg("element"::text, ','), ']')::json
  FROM (SELECT CASE row_number() OVER () - 1
                 WHEN "index_to_update" THEN to_json("value_to_update")
                 ELSE "element"
               END "element"
          FROM json_array_elements("json") AS "element") AS "elements"
$function$;

可以像UPSERT一样使用它(更新索引(如果存在)，或者创建索引(如果不存在-使用某个默认值填充未使用的索引)):

This one can be used, like an UPSERT (updates an index, if it exists, or creates, if not -- using some default value to fill up unused indexes):

CREATE OR REPLACE FUNCTION "json_array_set_index"(
  "json"            json,
  "index_to_set"    INTEGER,
  "value_to_set"    anyelement,
  "default_to_fill" json        DEFAULT 'null'
)
  RETURNS json
  LANGUAGE sql
  IMMUTABLE
  STRICT
AS $function$
SELECT concat('[', string_agg((CASE "index"
                                 WHEN "index_to_set" THEN to_json("value_to_set")
                                 ELSE COALESCE("json" -> "index", "default_to_fill")
                               END)::text, ','), ']')::json
  FROM generate_series(0, GREATEST("index_to_set", json_array_length("json") - 1)) AS "index"
$function$;

有了这些，您可以UPDATE任何json数据，例如:

With these, you can UPDATE any json data, like:

UPDATE plan_base
SET    atts = json_array_update_index(atts, 1, '{"planId":"71"}'::json)
WHERE  id = 46;

重要！:Json数组是从0索引的(不同于其他PostgreSQL数组).我的函数尊重这种索引.

Important! Json arrays are indexed from 0 (unlike other PostgreSQL arrays). My functions respect this kind of indexing.

SQLFiddle

有关更新JSON对象的更多信息:

More about updating a JSON object:

更新:现在压缩了功能.

相关推荐