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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Ya agreed thats not legal JSON, that was typing mistake sorry for that,

let me make you clear what i need exactly,

I have table named (exampleTable) with json field as (example_list), and
when i say
SELECT * FROM exampleTable;

id |
example_list
---+----------------------------------------------
2 | {"abc":[ { "a":"b","c":"d" } ] }

And this data i am inserting through DJango view by writing the following
statement

test = ExampleTable(id = 2, example_list = {"abc" : [ { "a":"b","c":"d" } ]
})
test.save()

now i want to append { "e":"f", "g":"h" } to example_list by specifying the
id.
and after appending the data should be stored in the following way :
After appending,
for example if i say :
SELECT * FROM exampleTable;
i should get this.

id |
example_list
---+--------------------------------------------------------
2 | {"abc":[ { "a":"b","c":"d" }, { "e":"f", "g":"h" } ] }

Hope your clear now.

So now Guide me to append it through Python Djnago View. or through raw sql
query.

On Wed, Oct 29, 2014 at 12:45 PM, Michael Paquier <michael.paquier@gmail.com

wrote:

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

--
Regards :
Venktesh Guttedar.

On 10/29/2014 01:06 AM, VENKTESH GUTTEDAR wrote:

Ya agreed thats not legal JSON, that was typing mistake sorry for that,

let me make you clear what i need exactly,

I have table named (exampleTable) with json field as (example_list), and
when i say
SELECT * FROM exampleTable;

id | example_list
---+----------------------------------------------
2 | {"abc":[ { "a":"b","c":"d" } ] }

And this data i am inserting through DJango view by writing the
following statement

test = ExampleTable(id = 2, example_list = {"abc" : [ { "a":"b","c":"d"
} ] })
test.save()

now i want to append { "e":"f", "g":"h" } to example_list by specifying
the id.
and after appending the data should be stored in the following way :
After appending,
for example if i say :
SELECT * FROM exampleTable;
i should get this.

id | example_list
---+--------------------------------------------------------
2 | {"abc":[ { "a":"b","c":"d" }, { "e":"f", "g":"h" } ] }

Hope your clear now.

So now Guide me to append it through Python Djnago View. or through raw
sql query.

If it where me I would bring the data into the view and do the work
there in Python using dicts and lists. There is a Python module out
there that make this easier to do:

https://pypi.python.org/pypi/django-jsonfield

--
Regards :
Venktesh Guttedar.

--
Adrian Klaver
adrian.klaver@aklaver.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

@Adrian Klaver, Thanks for this idea but still m really confused with how
to update the json filed in the DB. is there any way to update the json
field in the DB through view.?

On Wed, Oct 29, 2014 at 7:37 PM, Adrian Klaver <adrian.klaver@aklaver.com>
wrote:

On 10/29/2014 01:06 AM, VENKTESH GUTTEDAR wrote:

Ya agreed thats not legal JSON, that was typing mistake sorry for that,

let me make you clear what i need exactly,

I have table named (exampleTable) with json field as (example_list), and
when i say
SELECT * FROM exampleTable;

id | example_list
---+----------------------------------------------
2 | {"abc":[ { "a":"b","c":"d" } ] }

And this data i am inserting through DJango view by writing the
following statement

test = ExampleTable(id = 2, example_list = {"abc" : [ { "a":"b","c":"d"
} ] })
test.save()

now i want to append { "e":"f", "g":"h" } to example_list by specifying
the id.
and after appending the data should be stored in the following way :
After appending,
for example if i say :
SELECT * FROM exampleTable;
i should get this.

id | example_list
---+--------------------------------------------------------
2 | {"abc":[ { "a":"b","c":"d" }, { "e":"f", "g":"h" } ] }

Hope your clear now.

So now Guide me to append it through Python Djnago View. or through raw
sql query.

If it where me I would bring the data into the view and do the work there
in Python using dicts and lists. There is a Python module out there that
make this easier to do:

https://pypi.python.org/pypi/django-jsonfield

--

Regards :
Venktesh Guttedar.

--
Adrian Klaver
adrian.klaver@aklaver.com

--
Regards :
Venktesh Guttedar.

On 10/29/2014 11:26 PM, VENKTESH GUTTEDAR wrote:

@Adrian Klaver, Thanks for this idea but still m really confused with
how to update the json filed in the DB. is there any way to update the
json field in the DB through view.?

Sure, how you would normally update a value. Assuming id is unique:

id2 = ExampleTable.objects.get(id=2)

id2.example_list

You now have the example_list and you can do what you want with it. What
that is depends on what field type you have declared example_list in
your model? That is why I suggested the jsonfield in a previous post, it
automatically converts Python data structures into JSON and the reverse.

Then:

id2.save()

In your Django project I would crank up:

python manage.py shell

and work with the model directly to see what is happening.

--
Regards :
Venktesh Guttedar.

--
Adrian Klaver
adrian.klaver@aklaver.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On 10/29/2014 11:26 PM, VENKTESH GUTTEDAR wrote:

@Adrian Klaver, Thanks for this idea but still m really confused with
how to update the json filed in the DB. is there any way to update the
json field in the DB through view.?

Realized I should have shown at least one concrete example so, assuming
you are dealing with Python data structures where:

id2.example_list = {"abc" : [ { "a":"b","c":"d" } ] }

then

id2.example_list["abc"].append({ "e":"f", "g":"h" })

id2.example_list

{'abc': [{'a': 'b', 'c': 'd'}, {'e': 'f', 'g': 'h'}]}

then

Regards :
Venktesh Guttedar.

--
Adrian Klaver
adrian.klaver@aklaver.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Thanks Adrian Klaver, Its really helped me to solve my problem, ya i have
example_list = jsonfield.JSONField() in models. Apologies i did not mention
that. but anyways thank again.

On Thu, Oct 30, 2014 at 8:09 PM, Adrian Klaver <adrian.klaver@aklaver.com>
wrote:

On 10/29/2014 11:26 PM, VENKTESH GUTTEDAR wrote:

@Adrian Klaver, Thanks for this idea but still m really confused with
how to update the json filed in the DB. is there any way to update the
json field in the DB through view.?

Realized I should have shown at least one concrete example so, assuming
you are dealing with Python data structures where:

id2.example_list = {"abc" : [ { "a":"b","c":"d" } ] }

then

id2.example_list["abc"].append({ "e":"f", "g":"h" })

id2.example_list

{'abc': [{'a': 'b', 'c': 'd'}, {'e': 'f', 'g': 'h'}]}

then

Regards :

Venktesh Guttedar.

--
Adrian Klaver
adrian.klaver@aklaver.com

--
Regards :
Venktesh Guttedar.