Thread: Manipulating jsonb
Hello,
I'm playing with a 9.4 preview and the upcoming jsonb datatype. But
I'm missing the basic modification syntax.
Given the following situation:
CREATE TABLE test(key int, jsonval jsonb);
INSERT INTO test VALUES(1, '{"a": 1, "c": 3}');
How can I UPDATE that row with '{"b": 2, "d": 4}' ? Something like
this does not work:
UPDATE test SET jsonval = jsonval || '{"a": 1, "c": 3}'::jsonb
where key = 1;
The result should be
{"a": 1, "b": 2, "c": 3, "d": 4}
The same goes for removing values.
Did I overlook something obvious or is there really no way to do that right now?
Best regards,
Andreas Heiduk
Andreas, take a look on https://github.com/erthalion/jsonbx. This is a place, where all hstore functionality will be eventually ported. See this table - https://gist.github.com/erthalion/10890778 Oleg On Fri, May 2, 2014 at 10:32 PM, Andreas Heiduk <asheiduk@gmail.com> wrote: > Hello, > > I'm playing with a 9.4 preview and the upcoming jsonb datatype. But > I'm missing the basic modification syntax. > > Given the following situation: > > CREATE TABLE test(key int, jsonval jsonb); > INSERT INTO test VALUES(1, '{"a": 1, "c": 3}'); > > How can I UPDATE that row with '{"b": 2, "d": 4}' ? Something like > this does not work: > > UPDATE test SET jsonval = jsonval || '{"a": 1, "c": 3}'::jsonb > where key = 1; > > The result should be > > {"a": 1, "b": 2, "c": 3, "d": 4} > > The same goes for removing values. > > Did I overlook something obvious or is there really no way to do that right now? > > Best regards, > Andreas Heiduk > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general
No way, Andreas ! But, we hope, Dimitri will release his extension before 9.4, so anybody could install it. Oleg On Sat, May 3, 2014 at 1:21 AM, Andreas Heiduk <asheiduk@gmail.com> wrote: > Hello Oleg, > > how are the odds that the '||' and '-' operators from jsonbx will be > included in the public 9.4 release? > > Andreas > > > On 2 May 2014 21:21, Oleg Bartunov <obartunov@gmail.com> wrote: >> Andreas, >> >> take a look on https://github.com/erthalion/jsonbx. This is a place, >> where all hstore functionality will be eventually ported. See this >> table - https://gist.github.com/erthalion/10890778 >> >> Oleg >> >> On Fri, May 2, 2014 at 10:32 PM, Andreas Heiduk <asheiduk@gmail.com> wrote: >>> Hello, >>> >>> I'm playing with a 9.4 preview and the upcoming jsonb datatype. But >>> I'm missing the basic modification syntax. >>> >>> Given the following situation: >>> >>> CREATE TABLE test(key int, jsonval jsonb); >>> INSERT INTO test VALUES(1, '{"a": 1, "c": 3}'); >>> >>> How can I UPDATE that row with '{"b": 2, "d": 4}' ? Something like >>> this does not work: >>> >>> UPDATE test SET jsonval = jsonval || '{"a": 1, "c": 3}'::jsonb >>> where key = 1; >>> >>> The result should be >>> >>> {"a": 1, "b": 2, "c": 3, "d": 4} >>> >>> The same goes for removing values. >>> >>> Did I overlook something obvious or is there really no way to do that right now? >>> >>> Best regards, >>> Andreas Heiduk >>> >>> >>> -- >>> Sent via pgsql-general mailing list (pgsql-general@postgresql.org) >>> To make changes to your subscription: >>> http://www.postgresql.org/mailpref/pgsql-general
Hello Oleg, how are the odds that the '||' and '-' operators from jsonbx will be included in the public 9.4 release? Andreas On 2 May 2014 21:21, Oleg Bartunov <obartunov@gmail.com> wrote: > Andreas, > > take a look on https://github.com/erthalion/jsonbx. This is a place, > where all hstore functionality will be eventually ported. See this > table - https://gist.github.com/erthalion/10890778 > > Oleg > > On Fri, May 2, 2014 at 10:32 PM, Andreas Heiduk <asheiduk@gmail.com> wrote: >> Hello, >> >> I'm playing with a 9.4 preview and the upcoming jsonb datatype. But >> I'm missing the basic modification syntax. >> >> Given the following situation: >> >> CREATE TABLE test(key int, jsonval jsonb); >> INSERT INTO test VALUES(1, '{"a": 1, "c": 3}'); >> >> How can I UPDATE that row with '{"b": 2, "d": 4}' ? Something like >> this does not work: >> >> UPDATE test SET jsonval = jsonval || '{"a": 1, "c": 3}'::jsonb >> where key = 1; >> >> The result should be >> >> {"a": 1, "b": 2, "c": 3, "d": 4} >> >> The same goes for removing values. >> >> Did I overlook something obvious or is there really no way to do that right now? >> >> Best regards, >> Andreas Heiduk >> >> >> -- >> Sent via pgsql-general mailing list (pgsql-general@postgresql.org) >> To make changes to your subscription: >> http://www.postgresql.org/mailpref/pgsql-general
Hello Oleg, that's a pitty. I think at least some server-side processing for jsonb would make a round package. But I will monitor Dimitri's extension. Thank you very much for your answer. Andreas On 3 May 2014 00:26, Oleg Bartunov <obartunov@gmail.com> wrote: > No way, Andreas ! > > But, we hope, Dimitri will release his extension before 9.4, so > anybody could install it. > > Oleg > > On Sat, May 3, 2014 at 1:21 AM, Andreas Heiduk <asheiduk@gmail.com> wrote: >> Hello Oleg, >> >> how are the odds that the '||' and '-' operators from jsonbx will be >> included in the public 9.4 release? >> >> Andreas >> >> >> On 2 May 2014 21:21, Oleg Bartunov <obartunov@gmail.com> wrote: >>> Andreas, >>> >>> take a look on https://github.com/erthalion/jsonbx. This is a place, >>> where all hstore functionality will be eventually ported. See this >>> table - https://gist.github.com/erthalion/10890778 >>> >>> Oleg >>> >>> On Fri, May 2, 2014 at 10:32 PM, Andreas Heiduk <asheiduk@gmail.com> wrote: >>>> Hello, >>>> >>>> I'm playing with a 9.4 preview and the upcoming jsonb datatype. But >>>> I'm missing the basic modification syntax. >>>> >>>> Given the following situation: >>>> >>>> CREATE TABLE test(key int, jsonval jsonb); >>>> INSERT INTO test VALUES(1, '{"a": 1, "c": 3}'); >>>> >>>> How can I UPDATE that row with '{"b": 2, "d": 4}' ? Something like >>>> this does not work: >>>> >>>> UPDATE test SET jsonval = jsonval || '{"a": 1, "c": 3}'::jsonb >>>> where key = 1; >>>> >>>> The result should be >>>> >>>> {"a": 1, "b": 2, "c": 3, "d": 4} >>>> >>>> The same goes for removing values. >>>> >>>> Did I overlook something obvious or is there really no way to do that right now? >>>> >>>> Best regards, >>>> Andreas Heiduk >>>> >>>> >>>> -- >>>> Sent via pgsql-general mailing list (pgsql-general@postgresql.org) >>>> To make changes to your subscription: >>>> http://www.postgresql.org/mailpref/pgsql-general