How to update jsonb string with PostgreSQL?

Question

I'm using PostgreSQL 9.4.5. I'd like to update a jsonb column.

My table is structured this way:

CREATE TABLE my_table (
  gid    serial PRIMARY KEY,
  "data" jsonb
);

JSON strings are like this:

{"files": [], "ident": {"id": 1, "country": null, "type ": "20"}}

The following SQL doesn't do the job (syntax error - SQL state = 42601):

UPDATE my_table SET "data" -> 'ident' -> 'country' = 'Belgium';

Is there a way to achieve that?

`UPDATE my_table SET "data" = jsonb_set("data", '{"ident", "country"}', '"Belgium"');` — Abelisto, Jun 28 '16 at 13:15
Actually duplicated: [How do I modify fields inside the new PostgreSQL JSON datatype?](http://stackoverflow.com/q/18209625/593144) — Abelisto, Jun 28 '16 at 13:17
I agree but the question in the post your refer to is about `json` type (not `jsonb`). Answers widely refer to `jsonb`otherwise. — wiltomap, Jun 28 '16 at 14:17
@Abelisto: Your query returns error message: *fonction jsonb_set(jsonb, unknown, unknown) doesn't exist*. `jsonb_set()` function seems to be new to v. 9.5 (and I'm still using 9.4.5 as mentionned in my post). — wiltomap, Jun 28 '16 at 14:28
Yep, I missed the `postgresql-9.4` tag. `jsonb_set` function was introduced in the 9.5 version. Dirty but easy way: `... SET "data" = regexp_replace("data"::text, , '"country":[^,^}]+', '"country": "Belgium"')::jsonb` — Abelisto, Jun 28 '16 at 14:44
Good this worked fine! I just didn't understand what's between brackets for the regex `^,^}` ... Could you help on this point? — wiltomap, Jun 28 '16 at 14:55
Any characters except comma and closing curve bracket. But remember that it is **dirty** solution and may fail for some JSON objects. — Abelisto, Jun 28 '16 at 15:05
@Abelisto Dirty indeed. For an almost equally dirty solution, but at least `json`-based, see my answer. — Patrick, Jun 28 '16 at 15:39

score 1 · Answer 1 · answered Jun 28 '16 at 17:13

Ok there are two functions:

create or replace function set_jsonb_value(p_j jsonb, p_key text, p_value jsonb) returns jsonb as $$
  select jsonb_object_agg(t.key, t.value) from (
    select 
      key, 
      case 
        when jsonb_typeof(value) = 'object' then set_jsonb_value(value, p_key, p_value)
        when key = p_key then p_value 
        else value 
      end as value from jsonb_each(p_j)) as t;
$$ language sql immutable;

First one just changes the value of the existing key regardless of the key path:

postgres=# select set_jsonb_value(
  '{"files": [], "country": null, "ident": {"id": 1, "country": null, "type ": "20"}}', 
  'country', 
  '"foo"');
                                   set_jsonb_value                                    
--------------------------------------------------------------------------------------
 {"files": [], "ident": {"id": 1, "type ": "20", "country": "foo"}, "country": "foo"}
(1 row)

create or replace function set_jsonb_value(p_j jsonb, p_path text[], p_value jsonb) returns jsonb as $$
  select jsonb_object_agg(t.key, t.value) from (
    select 
      key, 
      case
        when jsonb_typeof(value) = 'object' then set_jsonb_value(value, p_path[2:1000], p_value)
        when key = p_path[1] then p_value 
        else value 
      end as value from jsonb_each(p_j)
    union all
    select 
      p_path[1],
      case 
        when array_length(p_path,1) = 1 then p_value 
        else set_jsonb_value('{}', p_path[2:1000], p_value) end 
    where not p_j ? p_path[1]) as t;
$$ language sql immutable;

Second one changes the value of the existing key using the path specified or creates it if the path does not exists:

postgres=# select set_jsonb_value(
  '{"files": [], "country": null, "ident": {"id": 1, "type ": "20"}}', 
  '{ident,country}'::text[], 
  '"foo"');
                                   set_jsonb_value                                   
-------------------------------------------------------------------------------------
 {"files": [], "ident": {"id": 1, "type ": "20", "country": "foo"}, "country": null}
(1 row)

postgres=# select set_jsonb_value(
  '{"files": [], "country": null, "ident": {"id": 1, "type ": "20"}}', 
  '{ident,foo,bar,country}'::text[], 
  '"foo"');
                                            set_jsonb_value                                            
-------------------------------------------------------------------------------------------------------
 {"files": [], "ident": {"id": 1, "foo": {"bar": {"country": "foo"}}, "type ": "20"}, "country": null}
(1 row)

Hope it will help to someone who uses the PostgreSQL < 9.5
Disclaimer: Tested on PostgreSQL 9.5

score 0 · Answer 2 · answered Jun 28 '16 at 15:35

In PG 9.4 you are out of luck with "easy" solutions like jsonb_set() (9.5). Your only option is to unpack the JSON object, make the changes and re-build the object. That sounds very cumbersome and it is indeed: JSON is horrible to manipulate, no matter how advanced or elaborate the built-in functions.

CREATE TYPE data_ident AS (id integer, country text, "type" integer);

UPDATE my_table
SET "data" = json_build_object('files', "data"->'files', 'ident', ident.j)::jsonb
FROM (
    SELECT gid, json_build_object('id', j.id, 'country', 'Belgium', 'type', j."type") AS j
    FROM my_table
    JOIN LATERAL jsonb_populate_record(null::data_ident, "data"->'ident') j ON true) ident
WHERE my_table.gid = ident.gid;

In the SELECT clause "data"->'ident' is unpacked into a record (for which you need to CREATE TYPE a structure). Then it is built right back into a JSON object with the new country name. In the UPDATE that "ident" object is re-joined with the "files" object and the whole thing cast to a jsonb.

A pure thing of beauty -- just so long as speed is not your thing...

IMO the main disadvantage of the your solution is that you rebuilds the whole object without taking in attention its initial structure which may be different. — Abelisto, Jun 28 '16 at 16:10

Chris Travers · Answer 3 · 2016-06-29T06:49:42.687

-1

My previous solution relied on 9.5 functionality.

I would recommend instead either going with abelisto's solutions below or using pl/perlu, plpythonu, or plv8js to write json mutators in a language that has better support for them.

edited Jun 29 '16 at 06:49

answered Jun 28 '16 at 13:19

Chris Travers

25,424
6
65
182

Your query returns error message: *fonction jsonb_build_object(unknown, unknown) doesn't exist* – wiltomap Jun 28 '16 at 14:26
sorry, misread the docs, should be json_build_object and then cast the final result to jsonb. editing. – Chris Travers Jun 29 '16 at 06:46
hmmm || also is 9.5 only. – Chris Travers Jun 29 '16 at 06:47

How to update jsonb string with PostgreSQL?

3 Answers3