CODEWITHSUNDEEP
fluttersupabase-databasesupabase-flutter
user2868835
user2868835

Reputation: 1600

Flutter Supabase client - query on jsonb array of json objects not working

I have a Flutter web app that is querying a Supabase database via the Flutter Supabase client library.

One of my queries is not working when I try to query a jsonb column called 'tags' that holds an array of json records. For example:

[
  {
    "id": 22,
    "title": "test5",
  },
  {
    "id": 25,
    "title": "test6",
  }
]

When I construct a query to find all JSON records with the ID 22, the query returns nothing.

The fetch clause is like:

.eq('tags->id', 22)

The URL created by the client app includes a parameter like:

&tags-%3Eid=eq.22

Any suggestions?

Follow up question:

Further to some initial feedback I changed my jsonb 'tags'column to store the data in the following json data model:

{
  "tagEntries": [
    {
      "id": 22,
      "title": "test5",
      "userUid": "cc1b9ad0-c24b-4756-a884-7b8a5e18a5fb"
    }
  ]
}

When I try to query on the json data I'm using....

.eq('tags->tagEntries[*].id',22)

... and it still does not work.

Any suggestions??

Upvotes: 0

Views: 226

Answers (1)

Sameri11
Sameri11

Reputation: 2730

I think that .eq('tags->id', 22) doesn't because of data scheme. I bet it would've worked if your scheme looked like this:

{
  "tags": {
    "id": 22,
    "title": "test5"
  }
}

But, your json is not and object which field may be accessed like this, but an array. In that case, I guess, it's worth to try filter instead of eq, like this:

.filter('tags', 'cs', '{"id": 22}')

filter uses special (I would say "raw") Postgres syntax, it gives a little bit more flexibility, but should be used carefully.

cs stands for contains meaning json will be filtered to values (arrays, in that case) which has specific field with specific value.

Upvotes: 0

Related Questions