Re: jsonb_delete with arrays

On Mon, Nov 21, 2016 at 5:05 AM, Dmitry Dolgov <9erthalion6@gmail.com> wrote:

> On 15 November 2016 at 22:53, Magnus Hagander <magnus@hagander.net> wrote:

> Attached is an implantation of jsonb_delete that instead of taking a single key to remove accepts an array of keys (it still does just keys, so it's using the - operator, it's not like the path delete function that also takes an array, but uses a different operator).

> 

> In some simple testing of working through a real world usecases where we needed to delete 7 keys from jsonb data, it shows approximately a 9x speedup over calling the - operator multiple times. I'm guessing since we copy a lot less and don't have to re-traverse the structure.

I wonder, is it worth it to create some sort of helper function to handle both

deleting one key and deleting an array of keys (like `setPath` for `jsonb_set`,

`jsonb_insert` and `jsonb_delete_path`)? At first glance it looks like

`jsonb_delete` and `jsonb_delete_array` can reuse some code.

Speaking about the performance I believe it's the same problem as here [1],

since for each key the full jsonb will be decompressed. Looks like we need a

new set of functions to read/update/delete an array of elements at once.

[1]: https://www.postgresql.org/message-id/flat/1566eab8731.10193ac585742.5467876610052746443%40zohocorp.com

It can be partially related, but the usecase itself had jsonb in memory only and never stored on disk, so it's not the decompression itself. Shouldn't be deep parsing either as we just copy the data over. But it's a different angle on the same core problem, I think, which comes from the fact that jsonb is just "one value".

 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.com/