How to create RPC function that will update json column?

[jimmiejackson414]

I have a JSON column where I want to store an array of objects. Looking at #465 I'm not sure whether PATCH has been implemented yet for json columns, though someone mentions that it's possible to create an .rpc() function to manually handle it from the database level.

Say for example it's an array with between 0 and 5 objects, I'd want the ability to either update a specific object or insert an object if not present. What would a function like this look like?

[laurenceisla]

Yes, the PATCH method will update the whole JSON column. To update just parts of it, you could use jsonb_set in a function, but it will require the exact index in the array.

Please provide a bit more details on how you will be updating / inserting the fields inside the json array because I can't see an straight forward way to do so in PostgreSQL (or maybe I'm missing something).

[laurenceisla]

Maybe this query could help you. Doing something like this would update a single index in a JSON array:

create table api.test (
  id int primary key,
  json_val json
);

CREATE OR REPLACE FUNCTION api.update_test(id int, index int, val jsonb) returns void
AS $$
    UPDATE api.test
    SET json_val = jsonb_set(json_val::jsonb, array[$2]::text[], $3)
    WHERE api.test.id = $1;
$$ LANGUAGE sql;

I found an example using WITH ORDINALITY that could also help you get the index in a JSON array.

[jimmiejackson414]

@laurenceisla I'll provide some more context. I have a column called onboarding_steps, which is a jsonb array of objects. Inside each array are just two properties, so the array could look like this:

[
  {
    "page": "dashboard",
    "completion_date": "2022-07-18T17:10:42.254Z"
  }
]

Essentially I have a few pages where I want to track whether a user has completed onboarding for that specific page. On the frontend I have logic that checks to see whether it's been completed for that page, and if not, display the onboarding steps.

I'm realizing though that I don't actually need to perform an update on an object in the array, just insert a new object in the array if one that matches that page doesn't exist. So in the example above, if a user completes onboarding for the "todos" page, a new object would be inserted alongside the existing "dashboard" page object.

Would the above function handle that scenario?

[wolfgangwalther]

This really sounds like you should normalize this into a onboarding_steps table. Embedding this table will then give you the same array of objects. But you can insert into the table directly.