Why does sqlite3 `json_extract` not work with the `LIKE` operator?

Question

I'm trying to use sqlite as a key-value-store for a small project. Since I need to filter the data by specific content of the json I'm using json_extract to extract individual properties from it. However, for some reason the LIKE operator doesn't work on the result. Searching around I found that on MySQL you need to use json_unquote to get the raw string but on SQLite the documentation (section 4.4.) states that this is not needed if only one value is extracted. The = works fine. I've put together a minimal working example:

SQLite version 3.38.0 2022-02-22 18:58:40
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> CREATE TABLE test (key TEXT, value TEXT);
sqlite> INSERT INTO test VALUES('a','{"foo":"bar"}');
sqlite> SELECT * FROM test;
a|{"foo":"bar"}
sqlite> SELECT * FROM test WHERE json_extract(`value`, '$.foo') LIKE 'ba*';
sqlite> SELECT value, json_extract(`value`, '$.foo') AS foo FROM test;
{"foo":"bar"}|bar
sqlite> SELECT * FROM test WHERE json_extract(`value`, '$.foo') = 'bar';
a|{"foo":"bar"}

Am I missing something or does the LIKE operator not support this sepcific use case?

Thanks :)

Answer 1

Was my bad: * is not a wildcard, % is. Thanks @forpas