文章目录
- 1.问题
- 2.json_each()
- 简介
- 语法
- 示例
- 3.实现
- 参考文献
1.问题
假如入有一个如下的 SQLite 表。
id | tags
---- | -------------------
1 | ["love","sadness"]
2 | ["love"]
3 | ["happiness","joy"]
如果想查询 tag 列中包含 love 的所有行，在 MySQL 中有 JSON_CONTAINS 可以使用，比如下面的 SQL：
SELECT * from my_table WHERE JSON_CONTAINS(tags, '"love"')
但是在 SQLite 中并没有类似 JSON_CONTAINS 的函数。
2.json_each()
如果要在 SQLite 中判断一个JSON 数组中是否包含某个值，可以使用 SQLite 的 JSON1 扩展库。该扩展库提供了一些函数，可以帮助我们提取 JSON 数据中的元素和信息。
简介
其中 json_each() 函数是一个表值函数，类似的函数还有 json_tree()。
所谓的表值函数，指函数结果是由多行构成的一个虚拟表，而非单一的值。
json_each() 函数遍历指定 JSON 文档，为每个直接子元素生成一行，最终返回由所有的行组成的结果集。
语法
json_each(json, path)
json 必需的，一个 JSON 文档。path 可选的，一个路径表达式。
json_each() 函数返回一个具有以下列的结果集：
key
如果 JSON 为数组，则 key 列为数组的索引；如果 JSON 为对象，则 key 列为对象的成员名称；其他情况， key 列为 NULL。value
当前元素的值。type
当前元素的 JSON 类型。可能的值： 'null'、'true'、'false'、'integer'、'real'、'text'、'array'、'object'。 他们与 json_type() 函数相同。atom
如果 value 是 JSON 原始类型，atom 存储其对应的 SQL 值；否则该列为 NULL。id
标识此行唯一性的一个整数。parent
父元素的整数 ID。 此列永远为 NULL。fullkey
它是当前行元素的路径。path
当前行元素的父元素的路径。
示例
遍历一个 JSON 数组中的元素：
SELECT * FROM json_each('[1, 2, 3]');
key value type atom id parent fullkey path
--- ----- ------- ---- -- ------ ------- ----
0 1 integer 1 1 $[0] $
1 2 integer 2 2 $[1] $
2 3 integer 3 3 $[2] $
遍历一个 JSON 对象中的元素：
SELECT * FROM json_each('{"x": 1, "y": 2}');
key value type atom id parent fullkey path
--- ----- ------- ---- -- ------ ------- ----
x 1 integer 1 2 $.x $
y 2 integer 2 4 $.y $
遍历一个 JSON 数组中的通过路径指定的元素：
SELECT * FROM json_each('[{"x": 1, "y": 2}]', '$[0]');
key value type atom id parent fullkey path
--- ----- ------- ---- -- ------ ------- ----
x 1 integer 1 3 $[0].x $[0]
y 2 integer 2 5 $[0].y $[0]
3.实现
利用 json_each() 将数组展开，生成一个虚拟表。然后再判断该虚拟表中是否包含指定元素。
回到最初的问题，可以借助 json_each() 查询 JSON 数组包含指定元素的记录。
SELECT *
FROM my_table
WHERE EXISTS (SELECT 1 FROM json_each(tags) WHERE value = 'love')
ORDER BY id ASC;
结果：
id tags
-- -------------------
1 ["love","sadness"]
2 ["love"]
参考文献
JSON Functions And Operators - SQLite
Is there a method to check if an array includes one value in … - stackoverflow.com
SQLite json_each() 函数 - 数据库教程
