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

On Wed, Oct 29, 2014 at 3:42 PM, VENKTESH GUTTEDAR
<venkteshguttedar(at)gmail(dot)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