javascriptdatabasepostgresqlsupabasesupabase-js

How Can I Query User Via JSONB Data In Supabase?


This is a function that I wrote down to communicate with database and query admin users by a property that I call usertag which is unique for each user. Idk why the function returns null, I mentioned the full error message down. How can I query correctly?

async function queryUserrByUsertag(usertag) {
  try {
    // Fetch the user from the database based on the provided usertag
    const { data, error } = await supabase
      .from('users')
      .select('*')
      .eq('usertag: admin_data->>usertag', usertag);

    if (error) {
      console.error('Error querying users:', error.message);
      return null;
    }

    if (data && data.length > 0) {
      // The usertag should be unique, so there should be only one user
      const user = data[0];
      return user;
    } else {
      throw new Error('User not found'); // Throw an error if no user found
    }
  } catch (error) {
    console.error('Error fetching user by usertag:', error.message);
    return null;
  }
}

I got this error:

Error querying users: column users.usertag does not exist

That's how I added the superadmin user:

INSERT INTO users (first_name, last_name, email, phone_number, birth_date, strikes, admin_data)
VALUES (
  'Rami',
  'Issawe',
  'rmcsdfsdfsdfdsfsd.isdfdsfssawy@gmail.com',
  '+56456456465',
  '1996-11-21',
  NULL,
  '{"usertag": "3AbCdEfGhIj", "hashed_passcode": "$2a$16$O3SFrgYr3M0LnpTpTct8iuFOFk7GJOVbUjcJVtxqBd5Ml1DNhBQnm", "is_superadmin": true}'::jsonb
);

From here you can see that there is a column called admin_data which takes jsonb objects. I expect the function to return the user instead of null


Solution

  • The following line:

    .eq('usertag: admin_data->>usertag', usertag)
    

    should not have a space between usertag: and admin_data....

    The proper column naming syntax should read:

    .eq('usertag:admin_data->>usertag', usertag)