postgresql json jsonb 有什么区别

发布网友发布时间：2022-04-09 02:01

共2个回答

懂视网时间：2022-04-09 06:22

OR REPLACE FUNCTION "json_object_del_path"( "json" json, "key_path" TEXT[] ) RETURNS json LANGUAGE sql IMMUTABLE STRICT AS $function$ SELECT CASE WHEN ("json" -> "key_path"[l] ) IS NULL THEN "json" ELSE CASE COALESCE(array_length("key_path", 1), 0) WHEN 0 THEN "json" WHEN 1 THEN "json_object_del_key"("json", "key_path"[l]) ELSE "json_object_set_key"( "json", "key_path"[l], "json_object_del_path"( COALESCE(NULLIF(("json" -> "key_path"[l])::text, ‘null‘), ‘{}‘)::json, "key_path"[l+1:u] ) ) END END FROM array_lower("key_path", 1) l, array_upper("key_path", 1) u $function$;

CREATE OR REPLACE FUNCTION "json_object_del_key"(
 "json"  json,
 "key_to_del" TEXT
)
 RETURNS json
 LANGUAGE sql
 IMMUTABLE
 STRICT
AS $function$
SELECT CASE
 WHEN ("json" -> "key_to_del") IS NULL THEN "json"
 ELSE (SELECT concat(‘{‘, string_agg(to_json("key") || ‘:‘ || "value", ‘,‘), ‘}‘)
  FROM (SELECT *
   FROM json_each("json")
   WHERE "key" <> "key_to_del"
  ) AS "fields")::json
END
$function$;

CREATE OR REPLACE FUNCTION "json_object_update_key"(
 "json"  json,
 "key_to_set" TEXT,
 "value_to_set" anyelement
)
 RETURNS json
 LANGUAGE sql
 IMMUTABLE
 STRICT
AS $function$
SELECT CASE
 WHEN ("json" -> "key_to_set") IS NULL THEN "json"
 ELSE (SELECT concat(‘{‘, 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
END
$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 concat(‘{‘, string_agg(to_json("key") || ‘:‘ || "value", ‘,‘), ‘}‘)::json
 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‘::json
   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"
$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 concat(‘{‘, string_agg(to_json("key") || ‘:‘ || "value", ‘,‘), ‘}‘)::json
 FROM (SELECT *
  FROM json_each("json")
  WHERE "key" <> "key_to_set"
  UNION ALL
 SELECT "key_to_set", to_json("value_to_set")) AS "fields"
$function$;

CREATE OR REPLACE FUNCTION "json_object_set_path"(
 "json"  json,
 "key_path" TEXT[],
 "value_to_set" anyelement
)
 RETURNS json
 LANGUAGE sql
 IMMUTABLE
 STRICT
AS $function$
SELECT CASE COALESCE(array_length("key_path", 1), 0)
  WHEN 0 THEN to_json("value_to_set")
  WHEN 1 THEN "json_object_set_key"("json", "key_path"[l], "value_to_set")
  ELSE "json_object_set_key"(
  "json",
  "key_path"[l],
  "json_object_set_path"(
  COALESCE(NULLIF(("json" -> "key_path"[l])::text, ‘null‘), ‘{}‘)::json,
  "key_path"[l+1:u],
  "value_to_set"
  )
  )
 END
 FROM array_lower("key_path", 1) l,
 array_upper("key_path", 1) u
$function$;

usage:

s1=# SELECT json_object_del_key (‘{"hello":[7,3,1],"foo":{"mofu":"fuwa", "moe":"kyun"}}‘,
     ‘foo‘),
  json_object_del_path(‘{"hello":[7,3,1],"foo":{"mofu":"fuwa", "moe":"kyun"}}‘,
     ‘{"foo","moe"}‘);

 json_object_del_key |  json_object_del_path
---------------------+-----------------------------------------
 {"hello":[7,3,1]} | {"hello":[7,3,1],"foo":{"mofu":"fuwa"}}

Pgsql数据库jsonb操作函数集合

标签：

热心网友时间：2022-04-09 03:30

　　你好，JSONB 同时属于 JSON (JavaScript Object Notation) 数据类型，jsonb 和 json 的输入数据几乎完全通用，最大的差别体现在效率上，json 存储的数据几乎和输入数据一样，存储的是未解析的数据，调用函数时使用效率较低; 而 jsonb 存储的是分解的 binary 格式数据，使用时不需要再解析了，因此使用上效率较高; 另一方面 json 在写入时较快，而 jsonb 写入时由于需要转换导致写入较慢。希望能帮到你。