SQLite 判断 JSON 数组是否包含某元素

恋喵大鲤鱼

发布于 2023-10-12 15:35:35

6330

发布于 2023-10-12 15:35:35

文章被收录于专栏：C/C++基础

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() 函数 - 数据库教程

本文参与腾讯云自媒体同步曝光计划，分享自作者个人站点/博客。

原始发表：2023-06-06，如有侵权请联系 cloudcommunity@tencent.com 删除

json

本文分享自作者个人站点/博客前往查看

如有侵权，请联系 cloudcommunity@tencent.com 删除。

本文参与腾讯云自媒体同步曝光计划，欢迎热爱写作的你一起参与！

登录后参与评论

0 条评论

热度