https://supabase.com/ logo
Join Discord
Powered by
How do you create a column of arrays, like every r...
# help
r
How do you create a column of arrays, like every row in column has an array of objects?
s
You can use a jsonb column for this
r
can you please elaborate a bit. i also need to update those array based on the object properties in it.
for ex consider an array of tasks
Copy code
[
{
task:"do something",
status: "done"
},
{
task:"some more",
status: "pending"
}
]
i need to add task or update the status of existing tasks
s
You might not want to do that in a relational database, you would rather create a table with such columns
You can read more about jsonb column type in the Postgres docs https://www.postgresql.org/docs/current/datatype-json.html
r
i see. i was approaching it the wrong way. thank you so much. its really helps.
s
Yeah this happens if you are used to NoSQL document based databases. We should really create some guides around how to migrate from NoSQL Databases to Relational Databases
r
totally agree.
s
I hope no one in the team reads this, I might have just created some work for myself. 😂
r
hey, quick question, when you said create new table, did you mean a new table for each user, coz every user will have tasks array.
s
No sorry, what you would do is create a table for tasks and have a user_id column to create the relationship to the user
I'm going to paste the raw SQL here for a table creation example
Copy code
sql
-- Table Definition
CREATE TABLE "public"."todos" (
    "id" BIGSERIAL NOT NULL,
    "user_id" uuid NOT NULL,
    "task" text,
    "status" text DEFAULT 'pending'::text,
    "inserted_at" timestamptz NOT NULL DEFAULT timezone('utc'::text, now()),
    CONSTRAINT "todos_user_id_fkey" FOREIGN KEY ("user_id") REFERENCES "auth"."users"("id"),
    PRIMARY KEY ("id")
);
r
oh so all the tasks from all the users will be in the same table and I can access particular users's tasks with its userids
now i get it . thanks
s
Yeah correct
Then with the supabase-js library you can retrieve all of these tasks as an array of objects.
r
understood. I got so confused before. sorry for the trouble and thanks again.
s
No problem, ask away anytime.
PreviousNext
Powered by