allow updating with operators like '_inc', '_append' etc in upsert

[jvice152]

we have a scenario where we want to append/delete (or any of the supported jsonb operators actually) to a json b field. This is currently provided in the updates

https://docs.hasura.io/1.0/graphql/manual/mutations/update.html

but if we want to use the upsert approach, all that seems to be provided is regular sets to the json field entirely

https://docs.hasura.io/1.0/graphql/manual/mutations/upsert.html#upsert-in-nested-mutations

i would like to have a way to set the field if it doesn't exist, but on conflict do an append if possible?

thanks

[jvice152]

note this is different from https://github.com/hasura/graphql-engine/issues/210, as i want to do jsonb operations WITH an upsert

[coco98]

@jvice152 Makes sense. Could you also provide an example of a sample graphql mutation that would ideally work?

[jvice152]

hmmm this is a bit of a tricky one. WIth upserts, some implementation offers Two queries passed down, and insert statement and a seperate upstate statement if a collision occurs. That would be quite the departure from your current implementation.

But to keep the current implentation moving forward, perhaps a few jsonb operators tailored for upserts?

I cant see a use case for deleting these keys if exists, but create if it doesnt.. But i definitely can see the case (as we have it) for append or prepend if exists, or create if insert.

I think there are two strategies here... you could define it in the actual insert block, but i think adding the operations in OnConflict might be a little cleaner

mutation upsertAppend { insert_foo(objects: [{ id: 1, bar: {PropA: "2", PropB: "1"} }], on_conflict: { constraint: foo_pkey, _append: bar} ) { affected_rows returning { id bar } } }

[0x777]

@jvice152 Currently the upsert behaviour only allows you to 'set' something on conflict. In cases such as yours, you'll need a different operator, such as 'append'. We definitely need to add such common operators.

Syntax

you could define it in the actual insert block

This won't work as you get to specify different behaviour for each object that is being inserted and graphql-engine can't pass this behaviour to the sql insert statement.

But this looks nice:

mutation upsertAppend { 
  insert_foo(
    objects: [{ id: 1, bar: {PropA: "2", PropB: "1"} }], 
    on_conflict: { constraint: foo_pkey, _append: bar} ) { 
      affected_rows 
      returning { id bar } 
    } 
} 

I can also think of other operators like _increment, _decrement. In fact, we can support all update operators which take a value of type 't' and return a value of the same type 't'. In case of _append it is json(b) -> json(b), in case of _increment, it is int -> int.

[jvice152]

also, any chance of append accepting arrays, just like columns?

[0x777]

also, any chance of append accepting arrays, just like columns?

Yes. It'll be the same as update_columns which should ideally be changed to something like _set.

[jvice152]

@0x777 Not sure if this helps your approach, but check this out

http://blog.bguiz.com/2017/json-merge-postgresql/

[dsandip]

@jvice152 we want your help with finalising the scope for this, and would love to hop on a short call. If this works for you, could you please schedule a chat here? cc: @0x777

[jvice152]

@dsandip will do, thanks!

[paplco]

Hi, wanting to perform an _inc during an upsert if the row already exists, instead of running two separate database queries. Are there any updates on this feature request?

Thanks

[chaselee]

Looks like this was reprioritized in April to a lower priority, shortly after the last comment. I'm not familiar with the Hasura codebase yet but if someone could call out where to look for potential contributors it could help save this from issue purgatory.

[SameerChorge94]

Hii Is there any update on this feature request ??

we really need the _append / _prepend options on the insert (with onConflict ) mutations. Or any workaround for now to handle update/insert in single mutation with hasura for jsonB fields _append and _prepend

[nrutman]

Would really love this feature. +1

[f5io]

is there any intention to follow this through, this is extremely important for us...

[carissacks]

it'd be helpful to have this!! ❤️