Query a List<String> with json

[bobekos]

I have this table and this List converter:

//the table
class StockArticleLocalModel extends Table {
  TextColumn get uuid => text()();

  TextColumn get inventoryId => text()();

  TextColumn get description => text()();

  TextColumn get alternativeIds =>
      text().map(const DatabaseStringListConverter())();

  @override
  Set<Column>? get primaryKey => {uuid};
}

//the converter
class DatabaseStringListConverter extends TypeConverter<List<String>, String> {
  const DatabaseStringListConverter();

  @override
  List<String> fromSql(String fromDb) {
    final list = json.decode(fromDb) as List;

    return list.map((e) => e as String).toList();
  }

  @override
  String toSql(List<String> value) {
    return json.encode(value);
  }
}

Now how to query the alternativeIds to know if the list contains a specific string?

This was my approach (with json_each) ->

(select(transferArticleLocalModel)
      ..where((tbl) => tbl.alternativeIds.jsonEach(this).value.cast<String>().equals('$value')))
        .get();

But it didn't work.

Now i am using regex like this

(select(transferArticleLocalModel)
      ..where((tbl) => tbl.alternativeIds.regexp('"$value"')))
        .get();

but how to use the json api for that?

[simolus3]

In SQL, jsonEach essentially creates a short-lived virtual table for the duration of the statement, so it can't be used in a where clause as is (because those operate on single values instead of whole tables). Instead, you need to write a subquery selecting each element from the list. You can then use isInQuery to determine whether a target element is contained in that query.

To put it all together, the final query would probably look like

final alternativeIds = transferArticleLocalModel.alternativeIds.jsonEach(this);
final containedInAlternativeIds = Variable.withString(value).isInQuery(
    selectOnly(alternativeIds)..addColumns([alternativeIds.value]));
(select(transferArticleLocalModel)
      ..where((tbl) => containedInAlternativeIds))
        .get();