jsonb_set array append hack?

From: Thom Brown <thom(at)linux(dot)com>
To: PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org>
Subject: jsonb_set array append hack?
Date: 2015-09-14 17:29:36
Message-ID: CAA-aLv59dToy02HNQd2wvFO8S5ZpLwQwtnsdoCGKehp7tCHrxA@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Hi,

I've noticed that if you use a string for an element key in jsonb_set with
create_missing set to true, you can use it to append to an array:

postgres=# SELECT jsonb_set(
'{"name": "Joe", "vehicle_types": ["car","van"]}'::jsonb,
'{vehicle_types,nonsense}',
'"motorcycle"', true);
jsonb_set
----------------------------------------------------------------
{"name": "Joe", "vehicle_types": ["car", "van", "motorcycle"]}
(1 row)

What this really should match is a nested element inside "vehicle_types"
called "nonsense". But this seems to be a hack to get an element added to
an array. To do it properly currently requires specifying an arbitrary
number in the hope that it will exceed the number of elements you have in
the array.

e.g.

postgres=# SELECT jsonb_set(
'{"name": "Joe", "vehicle_types": ["car","van"]}'::jsonb,
'{vehicle_types,100000}',
'"motorcycle"', true);
jsonb_set
----------------------------------------------------------------
{"name": "Joe", "vehicle_types": ["car", "van", "motorcycle"]}
(1 row)

But I'm guessing people shouldn't be relying on the hack in the first
example. Isn't this a bug? If so, wouldn't this also be a bug?:

postgres=# SELECT jsonb_set(
'{"name": "Joe", "vehicle_types": ["car","van"]}'::jsonb,
array['vehicle_types',NULL],
'"motorcycle"', true);

Thom

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Alvaro Herrera 2015-09-14 17:52:09 Re: WIP: Make timestamptz_out less slow.
Previous Message Pavel Stehule 2015-09-14 17:27:15 Re: On-demand running query plans using auto_explain and signals