Updating a specific field within a JSONB column in Supabase in Flutter

Question

In my Flutter Web Application that uses Supabase, in the database there is a Respondent table with a logs field that is of type jsonb. In this logs field, it will be a fixed list with the following format { "created_at" : timestamp, "last_login" : timestamp, "profile_update" : timestamp }.

Using plain SQL statement, I have successfully inserted an entry in the logs field with these VALUES jsonb_build_object('created_at', CURRENT_TIMESTAMP, 'last_login', NULL, 'profile_update', NULL)

However, I need to update the last_login field only. There is this article that uses .raw. As mentioned from the example:

await Supabase.instance.client
    .from('your_table_name') // Replace with your actual table name
    .update({
      'address': Supabase.instance.client.raw('jsonb_set(address, \'{country}\', \'"the Netherlands"\')')
    })
    .match({'address->country': 'NL'})
    .execute();

However when I tried this approach, it cannot be debugged/run since there is no .raw() function.

I tried this one below, there was no error but nothing happens. But I am sure this function was invoked because it displayed the "Update here", but there was no response printed out.

Future<void> updateLastLogin(String id) async {
    try {
      print('Update here');
      final response = await supabase.from('respondents').update({
        'logs': {'last_login': DateTime.now()}
      }).eq('id', id);
      print(response);
    } catch (error) {
      throw Exception(error);
    }
  }

How can I update a specific field in jsonb? Thanks in advance!

Answer 1

I'm sure the Reddit post that uses .raw() function to solve your problem is just generated from ChatGPT as we have never had a raw() function or anything similar, and it also uses a .execute() function that has been removed from the SDK a long time ago.

If you know that your users will have the created_at, last_login, and profile_update fields, why not just create three different columns? It will solve all the issues that you have. It's always better to create columns if you have uniform data.