Question: In postgresql how to change the value in jsonb type cloumn?

Question

In postgresql how to change the value in jsonb type cloumn?

Answers 1
Added at 2016-12-29 08:12
Tags
Question

Now I want to use postgresql function to change the jsonb type cloumn's value,now I have a function achieve the type 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$;

but I unable to change the json type to jsonb,Now I want a function "jsonb_object_set_key" who can achieve, please help me,thank you very much.

Answers to

In postgresql how to change the value in jsonb type cloumn?

nr: #1 dodano: 2016-12-29 09:12

just change the return data type:

t=# DROP FUNCTION jsonb_object_set_key(json,text,anyelement);
DROP FUNCTION
t=# CREATE OR REPLACE FUNCTION "jsonb_object_set_key"(
t(#       "jsonb"          jsonb,
t(#       "key_to_set"    TEXT,
t(#       "value_to_set"  anyelement
t(#     )
t-#       RETURNS jsonb
t-#       LANGUAGE sql
t-#       IMMUTABLE
t-#       STRICT
t-#     AS $function$
t$#     SELECT COALESCE(
t$#       (SELECT ('{' || string_agg(to_json("key") || ':' || "value", ',') || '}')
t$#          FROM (SELECT *
t$#                  FROM jsonb_each("jsonb")
t$#                 WHERE "key" <> "key_to_set"
t$#                 UNION ALL
t$#                SELECT "key_to_set", to_jsonb("value_to_set")) AS "fields"),
t$#       '{}'
t$#     )::jsonb
t$#     $function$;
CREATE FUNCTION

here it goes:

t=# select pg_typeof(jsonb_object_set_key('{"a":3}','a',5));
 jsonb

alse if you have at least 9.5 concider using jsonb_set: https://www.postgresql.org/docs/current/static/functions-json.html

Source Show
◀ Wstecz