PostGreSQL 中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$;

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$;

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$;

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$;

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$;