How to listen to database changes with condition flutter supabase?

Question

How to listen to database changes with condition if the item in a json row in the supabase database?

supabase.from('countries')
  .stream(primaryKey: ['id'])
  .eq('user_ids', '???') //what code should I replace this line with
  .listen((List<Map<String, dynamic>> data) {
});

user_id column is a map in countries table, for example:

{
  "id": 1,
  "name": "Jeo",
  "is_number": false
}

I need to request only if the id = 3 in the user_id's map, what code should I replace to do that?

eq('user_id', '???')

Answer 1

So in your case the column user_id has JSON as data type and at top level the JSON objects have a key named id. And you want to select based on the JSON key id. Then try:

eq('user_id ->> id', '3')

It should be possible to use columnA -> key1 resp. columnA ->> key1 syntax for selecting the JSON keys (works also for select). -> will return a JSON object, ->> gives you a string.

See: https://www.postgresqltutorial.com/postgresql-tutorial/postgresql-json/