为什么 sqlite3 `json_extract` 不能与 `LIKE` 运算符一起使用？
我正在尝试使用 sqlite 作为小项目的键值存储。由于我需要按 json 的特定内容过滤数据，因此我使用 json_extract 从中提取各个属性。但是，由于某种原因，LIKE 运算符对结果不起作用。搜索周围我发现在 MySQL 上你需要使用 json_unquote 来获取原始字符串，但在 SQLite 上 文档（第 4.4 节） 指出，如果只提取一个值，则不需要这样做。 = 工作正常。我整理了一个最小的工作示例：
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"}
我是否遗漏了某些内容，或者 LIKE 运算符不支持此特定用例？
谢谢 ：）
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 :)
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
我的错是：
*不是通配符，%是。谢谢@forpasWas my bad:
*is not a wildcard,%is. Thanks @forpas