sqljsondatabasepostgresqlsupabase

Supabase raw_user_meta_data values stored with quotation marks (" ")


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?


Solution

  • 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