RPC custom function to add object to JSONB array?

Hey guys, sorry if this is the wrong place to ask but I figured I would shoot. This is definitely not my cup of tea and have been struggling with this for a bit, I typically figure stuff out on my own but idk on this one. Basically, I want to call an RPC function from front-end that will add an additional object to a JSONB array. Essentially, I want it to do exactly what this guy is describing https://stackoverflow.com/questions/57057980/add-new-object-to-json-array-under-jsonb-column

I've tried different variations but not getting it to work. Attached is what the column looks like and some functions I've tried that dont work altho i think syntax is wrong, lol's but even when I've had it right I'm getting a variety of different errors, haha.

create function upd_quests(address text, is_complete bool, progress int, quest_id int, noodz_name text) returns quests as $$
update quests
set character_quests = jsonb_set(character_quests, '{quests}', character_quests -> 'quests' || '{"address":"address","noodz_name":"noodz_name","is_complete":false, "progress": 50, "quest_id": 1}')
where address = upd_quests.address
returning *;
$$ language sql;

create function upd_quests(address text, is_complete bool, progress int, quest_id int, noodz_name text) returns quests as $$
update quests
set character_quests = jsonb_set(character_quests, '{quests}', character_quests -> 'quests' || '{"address":"address","noodz_name":"noodz_name","is_complete":false, "progress": 50, "quest_id": 1}')
where address = upd_quests.address
returning *;
$$ language sql;

create function upd_quests(address text, is_complete bool, progress int, quest_id int, noodz_name text) returns quests as $$
update quests
set character_quests = jsonb_set(character_quests, '{quests}', character_quests -> 'quests' || '{"address":"address","noodz_name":"noodz_name","is_complete":false, "progress": 50, "quest_id": 1}')
where address = upd_quests.address
returning *;
$$ language sql;

create function upd_quests(address text, is_complete bool, progress int, quest_id int, noodz_name text) returns quests as $$
update quests
set character_quests = jsonb_set(character_quests, '{quests}', character_quests -> 'quests' || '{"address":"address","noodz_name":"noodz_name","is_complete":false, "progress": 50, "quest_id": 1}')
where address = upd_quests.address
returning *;
$$ language sql;

how do I pass in the arguments into the set statement? From front-end if I want to say await supabase.rpc(update_quests1) etc
```
sendQuestToSupabase("0x9001", false, 0, 1, "Noodz#29")

` I want it to add the new object to the array so that for a user, it would be

{
  "quests": [
    {
      "address": "0x9001",
      "progress": 0,
      "quest_id": 0,
      "noodz_name": "Noodz#29",
      "is_complete": false
    },
       {
      "address": "0x9001",
      "progress": 0,
      "quest_id": 1,
      "noodz_name": "Noodz#29",
      "is_complete": false
    }, 
  ]
}

{
  "quests": [
    {
      "address": "0x9001",
      "progress": 0,
      "quest_id": 0,
      "noodz_name": "Noodz#29",
      "is_complete": false
    },
       {
      "address": "0x9001",
      "progress": 0,
      "quest_id": 1,
      "noodz_name": "Noodz#29",
      "is_complete": false
    }, 
  ]
}

{
  "quests": [
    {
      "address": "0x9001",
      "progress": 0,
      "quest_id": 0,
      "noodz_name": "Noodz#29",
      "is_complete": false
    },
       {
      "address": "0x9001",
      "progress": 0,
      "quest_id": 1,
      "noodz_name": "Noodz#29",
      "is_complete": false
    }, 
  ]
}

{
  "quests": [
    {
      "address": "0x9001",
      "progress": 0,
      "quest_id": 0,
      "noodz_name": "Noodz#29",
      "is_complete": false
    },
       {
      "address": "0x9001",
      "progress": 0,
      "quest_id": 1,
      "noodz_name": "Noodz#29",
      "is_complete": false
    }, 
  ]
}

Perhaps this isnt the best approach anyway, but figured I'd see what people have to say. Thanks so much!

Stack Overflow

Add new object to json array under jsonb column

I have jsonb datatype column "payload" in postgres table which has the value as below:

{"testEvents": [
{
"id": 113068,
"name1": "test",
"count": 15
...

RPC custom function to add object to JSONB array?

Similar Threads

Similar Threads

Similar Threads