Modify JSONB fields inside postgres

Question

suppose I’ve got a table

CREATE TABLE test (id integer NOT NULL, categories jsonb)

and I’ve got following element there:

insert into test (categories) values ('{"foo": 1, "bar": 5}');

I’d like to create function, which accepts an array of strings. If a string was found in JSON, then it should +1 its’ value – if not, it should add it with value = 1.

I created a stub of my function, which looks like this:

CREATE OR REPLACE FUNCTION update_json(varchar[]) RETURNS void AS $$
DECLARE
  key varchar;
  my_json jsonb := '{"foo": 5, "baz": 10}'::jsonb; -- for testing purposes, I am using static json here.
BEGIN
  -- FINISH FOLLOWING LOOP
  FOREACH key IN ARRAY $1 LOOP
    IF my_json->key IS NULL THEN
      RAISE NOTICE 'json = %', my_json->key;
      my_json->key := 1;
    ELSE
      my_json->key += 1;
    END IF;
  END LOOP;

  RAISE NOTICE 'json = %', my_json;
  -- UPDATE test SET categories = my_json WHERE id = 1;
END;
$$ LANGUAGE plpgsql;


SELECT update_json(ARRAY['foo', 'bar']);

I’d expect that my_json on the end would be: {"foo": 2, "bar": 1, "baz": 10}

I would be really grateful if someone could help me getting this function working.

PS: I’m using Postgres 9.4, and I can’t use 9.5 until it’ll get stable version.

It's possible with [simple SQL, from PostgreSQL 9.3+](http://stackoverflow.com/questions/18209625/how-do-i-modify-fields-inside-the-new-postgresql-json-datatype/23500670#23500670) (I'll update that answer soon, to mention the new 9.5 functionality too). — pozs, Oct 29 '15 at 15:46

score 0 · Answer 1 · answered Oct 29 '15 at 13:02

0

There is jsonbx extension which backports some json(b) functions and operators from 9.5 to 9.4 (in particular jsonb_set which you are interested in).

Have a look at: http://www.pgxn.org/dist/jsonbx/1.0.0/

answered Oct 29 '15 at 13:02

Radek Postołowicz

4,506
2
30
47

Modify JSONB fields inside postgres

1 Answers1