Add element to JSON object in Postgres

Question

I have a text field in a database (postgres 9.2.1) with a json blob in it. It looks something similar to this except all on a single line, obviously:

{
  "keyword": {
    "checked": "1",
    "label": "Keyword"
  },
  "agency_name": {
    "checked": "0",
    "label": "Agency Name"
  }
}

I need to add an element to json array so that it looks like this:

{
  "keyword": {
    "checked": "1",
    "label": "Keyword"
  },
  "something_new": {
    "checked": "1",
    "label": "Something New"
  },
  "agency_name": {
    "checked": "0",
    "label": "Agency Name"
  }
}

I'm not as concerned about the placement of the new array element. It could be after agency_name. Is there an easy way to do this in postgres?

Answer 1

If you upgrade to PG9.5.1, then you can use sql operator || to merge jsonb, example

select '{"a":1}'::jsonb || '{"a":2, "b":2}'::jsonb

will return {"a": 2, "b": 2}

If you can't upgrade to pg9.5.1, IMHO, doing the job in your code will be a better choice. You can parse old jsonb string as a map, and then update the map, then convert to string and update db-record.

And if we want to update (add) a JSONB field:

UPDATE <table>
SET <field-name> = <field-name> || '{"a": 1}'::jsonb
WHERE id = <some id>

Documentation detailing all JSON operations in Postgres 9.5

Answer 2

use || to merge jsonb and set the value to it

example:

origin:

in table a:

id | info

1 | {"aa":"bb"}

2 | {"aa":"cc"}

after executing:

update a set info = info::jsonb || ('{"id":' || id || '}' )::jsonb 

generates:

id | info

1 | {"aa":"bb","id":1}

2 | {"aa":"cc","id":2}

something_else:

1. use - ‘key’ to delete element in jsonb
2. merge will replace origin one if the two jsonb has same key

Answer 3

Even I had the same problem, I wanted to dynamically append new elements to jsonb[].

Assume column_jsonb[] = [{"name":"xyz","age":"12"}]

UPDATE table_name
   SET column_jsonb[] = array_append(column_jsonb[],'{"name":"abc","age":"22"}');

Result : [{"name":"xyz","age":"12"},{"name":"abc","age":"22"}]

Answer 4

PostgreSQL does not yet have much in the way of JSON support functions: all I can see are ones like array_to_json, which could be useful if there was a corresponding way to convert the original JSON into an array, which you could then manipulate to add that additional element before converting back to JSON.

Possibly the best thing is to use a PL language to manipulate the JSON. One obvious one would be PLV8, which provides JavaScript programming functionality in PostgreSQL. You would write a user-defined function in JavaScript which would manipulate the JSON blob accordingly:

• http://code.google.com/p/plv8js/wiki/PLV8

Of course, many other PL languages like Java, Python or Perl may be just as good at working with JSON data, and possibly easier to install on your system. User-defined functions can be written in each of these if you have them set up.

Answer 5

Version 9.5 provides jsonb_set function with create_missing=TRUE. In any other cases use the following hack for appending information:

SELECT (trim( trailing '}' from data::text) || ', "c":2}')::json

To add/replace new value using more correct way:

UPDATE t
SET data=t3.data

FROM t AS t1
INNER JOIN
(
  SELECT id, json_object_agg(t.k,t.v)
  FROM
    (
      SELECT *
      FROM (SELECT id, json_object_keys(data) as k, data->json_object_keys(data) as v FROM t) as t2
      WHERE t.k != 'c'

      UNION ALL
      SELECT id, 'c'::text as k, '"new value"'::json as v FROM t1
    ) as t3
  GROUP by id
) as t4 ON (t1.id=t4.id)

To remove key:

UPDATE t
SET data=t3.data

FROM t AS t1
INNER JOIN
(
  SELECT id, json_object_agg(t.k,t.v)
  FROM (SELECT id, json_object_keys(data) as k, data->json_object_keys(data) as v FROM t) as t2
  WHERE t.k != 'c'
  GROUP by id
) as t4 ON (t1.id=t4.id)

Answer 6

I had exactly this problem. This solution is fairly 'pure' object manipulation and I sort of prefer 'sql' functions to plpgsql. The main thing is to disaggregate using json_each - giving you a record - and then creating a record from the

CREATE OR REPLACE FUNCTION json_extend_object(
    input_object json,
    append_key text,
    append_object json)
  RETURNS json AS
$BODY$
    select json_object_agg (((json_val)::record).key, ((json_val)::record).value)
    from (
        select json_val 
        from (select json_each (input_object) as json_val) disaggr
        where ((json_val::record).key != append_key)
        union 
        select newvals
        from (
            select append_key, append_object
        ) newvals
    ) to_rows;
$BODY$
  LANGUAGE sql IMMUTABLE
  ;

Answer 7

Solution provided by Andrew Wolfe is good, but not compatible with postgres version < 9.4. And if you have a 9.4+ version, just use jsonb and || (concatenate) operator to add an element to json.

So, here is the 9.3 compatible version of json_extend_object:

CREATE OR REPLACE FUNCTION json_extend_object(
    input_object json,
    append_key text,
    append_object json)
  RETURNS json AS
$BODY$
    select ('{'||string_agg(''||to_json((json_val::record).key)||':'|| to_json((json_val::record).value), ',')||'}')::json
    from (
        select json_val 
        from (select json_each (input_object) as json_val) jsonvals
        where ((json_val::record).key != append_key)
    union 
        select newvals
        from (select append_key, append_object) newvals
    ) to_rows;
$BODY$
LANGUAGE sql IMMUTABLE;