2

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.

clooock
  • 123
  • 1
  • 10

0 Answers0