Обсуждение: Appending new data to existing field of Json data type
Hello,
As i am new to postgresql, i am learning through experimenting things.{ [ { a:b, b:c }, { e:f, g:h } ] }
I have Postgresql 9.3.5.
--
--
Regards :
Venktesh Guttedar.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
Ya agreed thats not legal JSON, that was typing mistake sorry for that,
let me make you clear what i need exactly,id | example_list
---+----------------------------------------------
2 | {"abc":[ { "a":"b","c":"d" } ] }
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" } ] }
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
@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:
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: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.
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
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
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.
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.