Re: Appending new data to existing field of Json data type

On Wed, Oct 29, 2014 at 3:42 PM, VENKTESH GUTTEDAR
<venkteshguttedar@gmail.com> wrote:
>     As i am new to postgresql, i am learning through experimenting things.
>
>     i have a table with json data type field, so there is some data for
> example :
>
>         { [ { a:b, b:c } ] }
>
>     and now if i append data then it should be like :
>
>         { [ { a:b, b:c }, { e:f, g:h } ] }
That's not legal JSON, no? A key needs to be appended for the array
defined, like that:
=# select '{"f1":[{ "a":"b", "b":"c" }]}'::json;
             json
-------------------------------
 {"f1":[{ "a":"b", "b":"c" }]}
(1 row)

>     Is there any way to achieve this. please help.!
>     I have Postgresql 9.3.5.
Er, you can use the concatenate operator || to achieve that:
=# select '{"a":"b","b":"c"}'||','||'{"e":"f","f":"g"}'::json;
              ?column?
-------------------------------------
 {"a":"b","b":"c"},{"e":"f","f":"g"}
(1 row)

You may prefer actually something that really merges everything, among
many methods here is one (not the fastest one, now on the top of my
mind):
=# with union_json as (
select * from json_each('{"a":"b","b":"c"}'::json)
  union all
select * from json_each('{"d":"e","e":"f"}'::json))
select '{'||string_agg(to_json(key)||':'||value, ',')||'}'
  from union_json;
             ?column?
-----------------------------------
 {"a":"b","b":"c","d":"e","e":"f"}
(1 row)

Regards,
--
Michael