How to query a list inside a json

[manojbcs98]

int varietyId; "data": { "varieties": [ 4, 5 ], }

Here i need to query all the entries where varietyId is present inside the "varieties" list. How can this be done?

[simolus3]

The best way to use this would be a query in a moor file. You'll also need to enable the json1 extension in your builder options.

You could do something like

import 'path/to/your/table/classes.dart';

findWithVariety(:variety AS INT): SELECT table.* 
  FROM table, json_each(json_extract(table.json_column, '$.data.varieties')) varieties
WHERE :variety IN varieties

Moor will then generate a Selectable<DataClass> findWithVariety(int variety) method which you can get() or watch().

Note that json1 is only available when using moor_ffi.

[manojbcs98]

Instead of querying in Moor file cant be done in DAO file? If it can be done in DAO file how it would be?

[simolus3]

At the moment this can't be done in Dart. json_extract essentially returns a new table, which is not supported by the Dart api at the moment. Supporting table-valued functions in Dart would probably be a large and complicated change.

[rodydavis]

Is it possible to have the generated classes return the list for the type?

Since varieties is a List<String> how would the table look with json_extract in a drift file?

[simolus3]

I should mention that json_each is now available in the Dart query builder as well, so the issue should be addressed with that. The last example in this section is using JSON joins as a filter.

Is it possible to have the generated classes return the list for the type?

Do you mean when something based on json_each or json_extract is used as a result column in a query? If you can write a type converter for the inner field, you can apply it to the result column with MAPPED BY, like shown here for example.

[rodydavis]

Yep that's what I ended up doing (MAPPED BY) and worked well 👍🏼