Обсуждение: 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

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

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