1

I have the following table, postgresql 9.4

  create table test( id serial, data json);
  insert into test (data) values ('{"a":1, "b":['{"b1":1,"b2":2},{"b1":10,"b2":20}']}');

Now I need to update the value of b1, but only when the current value of b1 is 10 (not when value is 1). I have read various pages (A) How do I modify fields inside the new PostgreSQL JSON datatype? and about the jsonb function in 9.5 (B) http://michael.otacoo.com/postgresql-2/postgres-9-5-feature-highlight-new-jsonb-functions/

What I have not understood from those explanations is how I integrate my where clause so it updates the exact key I need to update. reading from A the set function and the examples:

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

How do I use it with a where clause ? will this be possibile in 9.5 using jsonb_set? my current solution is to get the json from the database, modify it in my program and update the column with the new json. It won't happen often, so it does not cost much. But I would prefer a more elegant solution, if I can understand how to do it thanks a lot

Community
  • 1
  • 1
Pat070
  • 335
  • 1
  • 3
  • 13

1 Answers1

0

I'm not sure about json but you can modify the jsonb like this;

update objects set body=jsonb_set(body, '{name}', '"Mary"', true) where id=1;

where body is a jsonb col

Teo Choong Ping
  • 12,512
  • 18
  • 64
  • 91
  • coming back a long time after. this does not work, because I need to check "id" when id is part of the jsonb not outside. I kept my external function, since it is done so seldom that it does not cost much. N elegant but works – Pat070 Jun 18 '17 at 14:09