Supabase raw_user_meta_data values stored with quotation marks (" ")

Question

I have a function in Supabase like this:

declare
  username text;
  name text;
begin

  insert into public.users (id, email, username, name, role)
values (new.id, new.email, new.raw_user_meta_data -> 'username', name = new.raw_user_meta_data -> 'name', 'student');

  return new;
end;

The function is used for inserting additional data into public.users table after Sign Up.

I tested the function by executing auth and send the body with Postman like this:

{
  "email": "[email protected]",
  "password": "asdflk;jsadfkl;j",
  "data": {
    "username":"Tester",
    "name":"Tester number one"
  }
}

The data stored must be Tester and Tester number one right?

But the data I found in public.users table is "Tester" and "Tester number one"

How can I fix this?

Answer 1

The -> operator yields json/jsonb. Cast to text, it preserves the double-quotes. If you want text value out of the raw_user_metda_data, use ->> instead. Demo at db<>fiddle:

declare
  username text;
  name text;
begin

  insert into public.users (id, email, username, name, role)
values (new.id, new.email, new.raw_user_meta_data ->> 'username', name = new.raw_user_meta_data ->> 'name', 'student');

  return new;
end;

select ('{"email": "[email protected]",
          "password": "asdflk;jsadfkl;j",
          "data": {
            "username":"Tester",
            "name":"Tester number one" }
         }'::jsonb->'data'->'username')::text as "jsonb cast to text";

jsonb cast to text
"Tester"

select '{"email": "[email protected]",
          "password": "asdflk;jsadfkl;j",
          "data": {
            "username":"Tester",
            "name":"Tester number one" }
         }'::jsonb->'data'->>'username' as "text extracted from jsonb";

text extracted from jsonb
Tester