SQLite数据库JSON功能增强解析(2022版)

毛炎宝Gardener

于 2025-06-05 09:14:13 发布

阅读量228

点赞数 4

CC 4.0 BY-SA版权

本文链接：https://blog.youkuaiyun.com/gitblog_00416/article/details/148442901

SQLite数据库JSON功能增强解析(2022版)

sqlite sqlite/sqlite: 是 SQLite 数据库的管理和维护工具，它包括 SQLite 的源代码、文档和其他工具。适合用于 SQLite 数据库的开发和维护，特别是对于需要使用和开发 SQLite 的场景。特点是 SQLite 源代码和文档、适用于多种平台。项目地址: https://gitcode.com/gh_mirrors/sq/sqlite

前言

SQLite作为一款轻量级的关系型数据库，近年来不断增强其对JSON数据的支持能力。2022年初，SQLite对JSON功能进行了重要升级，本文将深入解析这些增强特性，帮助开发者更好地利用SQLite处理JSON数据。

一、新增操作符->和->>

SQLite新增了两个JSON操作符，与MySQL和PostgreSQL保持兼容：

1. 操作符功能对比

->操作符：始终返回JSON格式数据
->>操作符：将结果转换为基本SQL数据类型(TEXT/INTEGER/REAL/NULL)
json_extract()函数：根据提取内容自动返回JSON或基本类型

2. 类型处理差异

与其他数据库系统不同，SQLite的->>操作符能根据JSON值的实际类型返回对应SQL类型：

-- 返回INTEGER类型
SELECT '{"age":30}'->>'$.age'; 

-- 返回REAL类型
SELECT '{"price":19.99}'->>'$.price';

-- 返回TEXT类型
SELECT '{"name":"张三"}'->>'$.name';

这种智能类型转换是SQLite特有的优势，因为其类型系统能根据值内容动态确定返回类型。

3. 返回值对照表

| JSON示例 | 路径 | ->返回值 | ->>返回值(SQLite) | json_extract()返回值 | |---------------------|-------|----------|-------------------|----------------------| | {"age":30} | $.age | '30' | 30(INTEGER) | 30(INTEGER) | | {"price":19.99} | $.price | '19.99' | 19.99(REAL) | 19.99(REAL) | | {"name":"张三"} | $.name | '"张三"' | '张三'(TEXT) | '张三'(TEXT) | | {"active":null} | $.active | 'null' | NULL | NULL |

二、兼容PostgreSQL的简化路径表达式

SQLite新增了对PostgreSQL风格简化路径表达式的支持：

1. 路径表达式对比

完整路径：'$.key'或'$[index]'（兼容MySQL）
简化路径：'key'或index（兼容PostgreSQL）

2. 兼容性对照表

| 表达式示例 | MySQL支持 | PostgreSQL支持 | SQLite支持 | |---------------------|-----------|----------------|------------| | data->'$.key' | 是 | 否 | 是 | | data->'key' | 否 | 是 | 是 | | array->'$[1]' | 是 | 否 | 是 | | array->1 | 否 | 是 | 是 |

注意：简化路径仅适用于->和->>操作符，json_extract()等函数仍需完整路径。