I'm using Flutter and Supabase and I'm trying to fetch all elements in a table that partially match an array field.
I have a table with a jsonb array column that contains a list of objects (PollItem
) converted in a json map as
Map<String, dynamic> toJson() {
return {
'player': player.toJson(),
'votes': votes,
'description' : description
};
}
player is another object (Player
) converted in a map in the same way. I insert the object in the database column with
await dbClient.from('polls')
.insert([{
...
"items" : poll.items.map((i) => i.toJson()).toList(),
...
])
.execute()
and it works. In the supabase column field the result is something like
[
{
"votes": 0,
"player": {
"id": "409",
"name": "INSIGNE",
"team": "Napoli"
},
"description": ""
},
{
"votes": 0,
"player": {
"id": "2530",
"name": "IBRAHIMOVIC",
"team": "Milan"
},
"description": ""
}
]
Now I want to select from the same table all rows that partially match items
array items. From a list of PollItem
I want to find out if there is some row in the database in which there is at least one match in the items column.
I tried different ways but I always get some errors.
// Method 1
await dbClient.from("polls")
.select()
...
.contains('items', poll.items.map((i) => i.toJson()).toList())
.execute()
I get following error:
[GETX] Error: DbRequest Instance of 'DbRequest' invalid input syntax for type json
// Method 2
await dbClient.from("polls")
.select()
...
.contains('items', jsonEncode(poll.items))
.execute()
I get following error:
[GETX] "Error: DbRequest Instance of 'DbRequest' malformed array literal: "[{"player":{"id":"409","name":"INSIGNE","team":"Napoli"},"votes":0,"description":""},{"player":{"id":"2530","name":"IBRAHIMOVIC","team":"Milan"},"votes":0,"description":""}]""
What am I doing wrong? Thank you in advance.