CODEWITHSUNDEEP
fluttersupabase-flutter
user2868835
user2868835

Reputation: 1600

Flutter Supabase query/filter on JSONB array

I have a Flutter app that is using a Supabase backend.

In one table, I have a JSONB column called 'tags' that holds a json array like....

[
   {"id":3,"title":"health"},
   {"id":4,"title":"sport",},
   {"id":5,"title":"food"}
]

I can't find a way of building a Flutter Supabase query that will retrieve the record if one of array entries matches a given title.

For example, retrieve the record if one of the json array entries has a title equal to "health"

I've tried

.eq("tags->>title",'health')

... but this doesn't work, probably because it is looking for a top level 'title' json property. However, I can't find a way of referencing json array elements.

I've even tried injecting a json name for the array. like...

{
  "tagEntries": [
    {
      "id": 4,
      "title": "water",
    },
    {
      "id": 5,
      "title": "black",
    }
  ]
}

...and querying with...

.eq("tags->>tagEntries->>title",'health')

... but this fails with a query syntax error

Any suggestions?

Upvotes: 1

Views: 538

Answers (1)

user2868835
user2868835

Reputation: 1600

Found the answer...

1). Use a top level json property to hold the array of json entries, like 'tagEntries' shown above.

  1. Use the following client side query with the 'like' function

    .like('tags->>tagEntries[*].title','health');

Upvotes: 0

Related Questions