PostGreSQL 中Json Array 赋值方法总结

Json 数据类型在PostGresql中无疑是其较其他关系型数据库的一大亮点，可以存储对象，类似nosql的文档数据类型啦，当然现在MysqL的最新版本中也包含了Json数据类型，所以好好总结下Json的用法还是很有必要的，这里主要针对Json Array 的赋值方法做个小结，因为在项目中用的还蛮多。

1、更新指定索引下json值（json array）

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 COALESCE( (SELECT ('[' || string_agg("element"::text,',') || ']') 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"),'[]' )::json $function$;

2、设置指定索引下的json值，没有则设置默认值（json array）

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 COALESCE( (SELECT ('[' || string_agg("element"::text,') || ']') FROM (SELECT CASE "index" WHEN "index_to_set" THEN to_json("value_to_set") ELSE COALESCE("json" -> "index","default_to_fill") END "element" FROM generate_series(0,GREATEST("index_to_set",json_array_length("json") - 1)) AS "index") AS "elements"),'[]' )::json $function$;

3、设置指定键值下对应的数据（json）

CREATE OR REPLACE FUNCTION "json_object_set_key"( "json" json,"key_to_set" TEXT,"value_to_set" anyelement ) RETURNS json LANGUAGE sql IMMUTABLE STRICT AS $function$ SELECT COALESCE( (SELECT ('{' || string_agg(to_json("key") || ':' || "value",') || '}') FROM (SELECT * FROM json_each("json") WHERE "key" <> "key_to_set" UNION ALL SELECT "key_to_set",to_json("value_to_set")) AS "fields"),'{}' )::json $function$;

4、设置键值数组对应的数组数据（批量更新）（json array）

CREATE OR REPLACE FUNCTION "json_object_set_keys"( "json" json,"keys_to_set" TEXT[],"values_to_set" anyarray ) RETURNS json LANGUAGE sql IMMUTABLE STRICT AS $function$ SELECT COALESCE( (SELECT ('{' || string_agg(to_json("key") || ':' || "value",') || '}') FROM (SELECT * FROM json_each("json") WHERE "key" <> ALL ("keys_to_set") UNION ALL SELECT DISTINCT ON ("keys_to_set"["index"]) "keys_to_set"["index"],CASE WHEN "values_to_set"["index"] IS NULL THEN 'null' ELSE to_json("values_to_set"["index"]) END FROM generate_subscripts("keys_to_set",1) AS "keys"("index") JOIN generate_subscripts("values_to_set",1) AS "values"("index") USING ("index")) AS "fields"),'{}' )::json $function$;

5、更新jsonb array 指定key下的json 数据，可以通过param进行指定赋值

CREATE OR REPLACE FUNCTION "jsonb_array_update_by_key"( "jsonb" jsonb,"key" text,"param" text,"value_to_update" anyelement ) RETURNS jsonb LANGUAGE sql IMMUTABLE STRICT AS $function$ SELECT COALESCE( (SELECT ('[' || string_agg("element"::text,') || ']') FROM (SELECT CASE "element"->>"key" WHEN "param" THEN "value_to_update"::jsonb ELSE "element" END "element" FROM jsonb_array_elements("jsonb") AS "element") AS "elements"),'[]' )::jsonb $function$;

以上内容并非全部自己创造，参考了stackoverflow一些大神的写法，进行部分改造。

原文链接：https://www.f2er.com/postgresql/194972.html