CODEWITHSUNDEEP
postgresqlsupabasesupabase-database
J. Jackson
J. Jackson

Reputation: 3774

How to create Postgres statement that updates or inserts objects into json array?

I'm working with Supabase which has the ability to create database functions. In a table, I have a json column which holds an array of simple objects, that are tracking a user's onboarding completion.

The json array might look something like this:

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

and so as you can see, it's only tracking two properties, page and completion_date. The issue I'm facing however, is that anytime a user goes to another page and completes the onboarding there, this value is being overwritten with the new values rather than inserted.

How do I go about inserting that new object if it doesn't exist, or updating that object based on the "page" key if it does already exist?

Upvotes: 0

Views: 493

Answers (1)

dshukertjr
dshukertjr

Reputation: 18680

If the structure of the data is already defined, I would create a separate table with page column with text type and completion_date as timestampz. This table most likely will also contain user_id column that represents the id of the user who has completed the step.

You could write a sql something like this to create the table:

create table if not exists public.onboarding_completion (
    user_id uuid references public.users on delete cascade not null,
    page text not null,
    completion_date timestamp with time zone default timezone('utc' :: text, now()) not null,
    primary key (user_id, page)
);

Notice that at the end I have defined a composite primary key, where the table makes sure that no row with the same combination of user_id and page will every exist.

Getting data from this table along with the user's profile is very easy.

const { data, error } = await supabase
  .from('users')
  .select('*, onboarding_completion(*)')
  .eq('id', user.id)
  .single()

Assuming user.id will get the currently logged in user, the above code will get the array of completed onboarding tasks in an array of objects just like how you wanted originally!

Having a separate table is a better approach, because you can make sure that your data will not be corrupted with unexpected data or bug on the frontend.

Upvotes: 1

Related Questions