简介

在 SQLite 版本 3.38.0 （2022-02-22） 之前，JSON 函数是一个扩展，需要另外编译。本 3.38.0 之后，JSON 函数和运算符默认内置于 SQLite 。

SQLite将JSON存储为普通文本，SQLite（目前）不支持JSON的二进制编码。

默认情况下，SQLite 支持十五个函数和两个运算符来处理 JSON 值

有15个标量函数和运算符。

1. json(json)
2. json_array(value1,value2,...)
3. json_array_length(json) / json_array_length(json,path)
4. json_extract(json,path,...)
5. json -> path
6. json ->> path
7. json_insert(json,path,value,...)
8. json_object(label1,value1,...)
9. json_patch(json1,json2)
10. json_remove(json,path,...)
11. json_replace(json,path,value,...)
12. json_set(json,path,value,...)
13. json_type(json) / json_type(json,path)
14. json_valid(json)
15. json_quote(value)

有两个聚合SQL函数。

1. json_group_array(value)
2. json_group_object(name,value)

这两个表值函数是：

1. json_each(json) / json_each(json,path)
2. json_tree(json) / json_tree(json,path)

JSON参数

对于接受 JSON 作为其第一个参数的函数，该参数可以是 JSON 对象、数组、数字、字符串或 null。

SQLite 数值和 NULL 值分别被解释为 JSON 数字和空值。

SQLite 文本值可以理解为 JSON 对象、数组或字符串。

如果将不是格式正确的 JSON 对象、数组或字符串的 SQLite 文本值传递给 JSON 函数，该函数会抛出错误。

PATH参数

对于接受PATH参数的函数,PATH必须是正确格式,否则函数将抛出一个错误。

格式正确的 PATH 是一个文本值，它正好以一个 '#39; 字符开头，后跟零个或多个".objectlabel"或"[arrayindex]" 的实例。

数组索引通常是非负整数 N。在这种情况下，所选的数组元素是数组的第 N 个元素，从左侧的零开始。数组索引也可以是“#-N”的形式，在这种情况下，所选元素是右侧的第N个元素。数组的最后一个元素是“#-1”。将“#”字符视为“数组中的元素数”。然后，表达式“#-1”的计算结果为与数组中最后一个条目对应的整数。数组索引有时只是 # 字符很有用，例如，在将值追加到现有 JSON 数组时：

json_set（'[0,1,2]','$[#]','new'） → '[0,1,2,"new"]'

VALUE参数

对于接受"value“参数（也显示为“value1“和”value2")，这些参数通常被理解为被引用并在结果中成为 JSON 字符串值的文字字符串。

即使输入value字符串看起来像格式正确的JSON,但它们在结果中仍被解释为字符串。

然而,如果一个value参数直接来自另一个 JSON 函数的结果或来自-> 运算符（但不是->> 运算符），则该参数被理解为实际 JSON 并插入完整的 JSON 而不是带引号的字符串。

json()函数

json(X)函数验证其参数X是一个有效的JSON字符串,并返回该JSON字符串的最小化版本(去掉所有不必要的空格)。

如果X不是一个格式正确的JSON字符串,那么这个例程会抛出一个错误。

json（'{"this"：" is","a"：["test"]}'）→'{"this"：" is","a"：["test"]}''

json_array()函数

json_array()SQL函数接受0个或多个参数,并返回一个由这些参数组成的格式正确的JSON数组。

如果json_array()的任何参数是一个BLOB，那么就会抛出一个错误。

json_array(1,2,'3',4) → '[1,2,"3",4]'
json_array('[1,2]') → '["[1,2]"]'
json_array(json_array(1,2)) → '[[1,2]]'
json_array(1,null,'3','[4,5]','{"six":7.7}') → '[1,null,"3","[4,5]","{\"six\":7.7}"]'
json_array(1,null,'3',json('[4,5]'),json('{"six":7.7}')) → '[1,null,"3",[4,5],{"six":7.7}]'

函数json_array_length()

json_array_length(X)函数返回JSON数组X中的元素数,如果X是数组以外的JSON值,则返回0。

X必须是正确是JSON格式。

json_array_length('[1,2,3,4]') → 4
json_array_length('[1,2,3,4]', '#39;) → 4
json_array_length('[1,2,3,4]', '$[2]') → 0
json_array_length('{"one":[1,2,3]}') → 0
json_array_length('{"one":[1,2,3]}', '$.one') → 3
json_array_length('{"one":[1,2,3]}', '$.two') → NULL

json_extract()函数

json_extract(X,P1,P2,...)从X处格式良好的JSON中提取并返回一个或多个值。

json_extract('{"a":2,"c":[4,5,{"f":7}]}', '#39;) → '{"a":2,"c":[4,5,{"f":7}]}'
json_extract('{"a":2,"c":[4,5,{"f":7}]}', '$.c') → '[4,5,{"f":7}]'
json_extract('{"a":2,"c":[4,5,{"f":7}]}', '$.c[2]') → '{"f":7}'
json_extract('{"a":2,"c":[4,5,{"f":7}]}', '$.c[2].f') → 7
json_extract('{"a":2,"c":[4,5],"f":7}','$.c','$.a') → '[[4,5],2]'
json_extract('{"a":2,"c":[4,5],"f":7}','$.c[#-1]') → 5
json_extract('{"a":2,"c":[4,5,{"f":7}]}', '$.x') → NULL
json_extract('{"a":2,"c":[4,5,{"f":7}]}', '$.x', '$.a') → '[null,2]'
json_extract('{"a":"xyz"}', '$.a') → 'xyz'
json_extract('{"a":null}', '$.a') → NULL

-> 和 ->> 运算符

从 SQLite 版本 3.38.0 (2022-02-22) 开始， -> 和 ->> 运算符可用于提取 JSON 的子组件。

-> 和 ->> 运算符的 SQLite 实现力求与 MySQL 和 PostgreSQL 兼容。

-> 和 ->> 运算符将 JSON 字符串作为左操作数，将 PATH 表达式或对象字段标签或数组索引作为右操作数。

-> 运算符返回所选子组件的 JSON 表示，如果该子组件不存在，则返回 NULL。

->> 运算符返回代表所选子组件的 SQL TEXT、INTEGER、REAL 或 NULL 值，如果子组件不存在，则返回 NULL。

'{"a":2,"c":[4,5,{"f":7}]}' -> '#39; → '{"a":2,"c":[4,5,{"f":7}]}'
'{"a":2,"c":[4,5,{"f":7}]}' -> '$.c' → '[4,5,{"f":7}]'
'{"a":2,"c":[4,5,{"f":7}]}' -> 'c' → '[4,5,{"f":7}]'
'{"a":2,"c":[4,5,{"f":7}]}' -> '$.c[2]' → '{"f":7}'
'{"a":2,"c":[4,5,{"f":7}]}' -> '$.c[2].f' → '7'
'{"a":2,"c":[4,5],"f":7}' -> '$.c[#-1]' → '5'
'{"a":2,"c":[4,5,{"f":7}]}' -> '$.x' → NULL
'[11,22,33,44]' -> 3 → '44'
'[11,22,33,44]' ->> 3 → 44
'{"a":"xyz"}' -> '$.a' → '"xyz"'
'{"a":"xyz"}' ->> '$.a' → 'xyz'
'{"a":null}' -> '$.a' → 'null'
'{"a":null}' ->> '$.a' → NULL

json_insert(),json_replace,and json_set()函数

json_insert()、json_replace和json_set()函数都以一个JSON值作为它们的第一个参数，后面跟着零个或多个路径和值参数对,并返回一个新的JSON字符串,这个字符串是通过更新输入的JSON的路径/值对形成的。

这些函数的不同之处仅在于它们如何处理创建新值和覆盖已有值。

json_insert('[1,2,3,4]','$[#]',99) → '[1,2,3,4,99]'
json_insert('[1,[2,3],4]','$[1][#]',99) → '[1,[2,3,99],4]'

json_insert('{"a":2,"c":4}', '$.a', 99) → '{"a":2,"c":4}'
json_insert('{"a":2,"c":4}', '$.e', 99) → '{"a":2,"c":4,"e":99}'

json_replace('{"a":2,"c":4}', '$.a', 99) → '{"a":99,"c":4}'
json_replace('{"a":2,"c":4}', '$.e', 99) → '{"a":2,"c":4}'

json_set('{"a":2,"c":4}', '$.a', 99) → '{"a":99,"c":4}'
json_set('{"a":2,"c":4}', '$.e', 99) → '{"a":2,"c":4,"e":99}'
json_set('{"a":2,"c":4}', '$.c', '[97,96]') → '{"a":2,"c":"[97,96]"}'
json_set('{"a":2,"c":4}', '$.c', json('[97,96]')) → '{"a":2,"c":[97,96]}'
json_set('{"a":2,"c":4}', '$.c', json_array(97,96)) → '{"a":2,"c":[97,96]}'

json_object()函数

json_object()SQL函数接受0个或多个参数对,并返回一个由这些参数组成的格式良好的JSON对象。

每对参数的第一个参数是标签,第二个参数是值。

如果json_object()的任何参数是一个BLOB,那么就会抛出一个错误。

json_object()函数目前允许重复的标签,不过这一点可能会在未来的改进中改变。

json_object('a',2,'c',4) → '{"a":2,"c":4}'
json_object('a',2,'c','{e:5}') → '{"a":2,"c":"{e:5}"}'
json_object('a',2,'c',json_object('e',5)) → '{"a":2,"c":{"e":5}}'

json_patch()函数

json_patch（T，P）SQL函数运行RFC-7396 MergePatch算法，将补丁P应用于输入T。返回T的补丁副本。

json_patch('{"a":1,"b":2}','{"c":3,"d":4}') → '{"a":1,"b":2,"c":3,"d":4}'
json_patch('{"a":[1,2],"b":2}','{"a":9}') → '{"a":9,"b":2}'
json_patch('{"a":[1,2],"b":2}','{"a":null}') → '{"b":2}'
json_patch('{"a":1,"b":2}','{"a":9,"b":null,"c":8}') → '{"a":9,"c":8}'
json_patch('{"a":{"x":1,"y":2},"b":3}','{"a":{"y":9},"c":8}') → '{"a":{"x":1,"y":9},"b":3,"c":8}'

json_remove()函数

json_remove(X,P,...)函数的第一个参数是一个JSON值,后面是0个或多个路径参数。

json_remove(X,P,...)函数返回一个X参数的副本,其中包含所有由路径参数标识的元素。

选择X中没有的元素的路径会被默默忽略。

从左到右依次进行删除。之前的删除造成的改变会影响后续参数的路径搜索。

如果调用json_remove(X)函数时没有路径参数,那么它将返回重新格式化的输入X,并删除多余的空白。

json_remove('[0,1,2,3,4]','$[2]') → '[0,1,3,4]'
json_remove('[0,1,2,3,4]','$[2]','$[0]') → '[1,3,4]'
json_remove('[0,1,2,3,4]','$[0]','$[2]') → '[1,2,4]'
json_remove('[0,1,2,3,4]','$[#-1]','$[0]') → '[1,2,3]'
json_remove('{"x":25,"y":42}') → '{"x":25,"y":42}'
json_remove('{"x":25,"y":42}','$.z') → '{"x":25,"y":42}'
json_remove('{"x":25,"y":42}','$.y') → '{"x":25}'
json_remove('{"x":25,"y":42}','#39;) → NULL

json_type()函数

json_type(X)函数返回X中最外层元素的 "类型"。json_type(X,P)函数返回X中被路径P选中的元素的 "类型"。json_type()返回的 "类型 "是以下SQL文本值之一。

null,"true","false","integer","real","text","array",或 "object"。如果json_type(X,P)中的路径P选择了一个在X中不存在的元素,那么这个函数返回NULL。

json_type（'{“ a”：[2,3.5，true，false，null，“ x”]}'）→'对象'
json_type（'{“ a”：[2,3.5，true，false，null，“ x”]}'，'#39;）→'object'
json_type（'{“ a”：[2,3.5，true，false，null，“ x”]}'，'$。a'）→'array'
json_type（'{“ a”：[2,3.5，true，false，null，“ x”]}'，'$。a [0]'）→'integer'
json_type（'{“ a”：[2,3.5，true，false，null，“ x”]}'，'$。a [1]'）→'real'
json_type（'{“ a”：[2,3.5，true，false，null，“ x”]}'，'$。a [2]'）→'true'
json_type（'{“ a”：[2,3.5，true，false，null，“ x”]}'，'$。a [3]'）→'false'
json_type（'{“ a”：[2,3.5，true，false，null，“ x”]}'，'$。a [4]'）→'null'
json_type（'{“ a”：[2,3.5，true，false，null，“ x”]}'，'$。a [5]'）→'text'
json_type（'{“ a”：[2,3.5，true，false，null，“ x”]}'，'$。a [6]'）→NULL

json_valid()函数

json_valid(X)函数如果参数X是格式正确的JSON,则返回1,如果参数X不是格式正确的JSON,则返回0。

json_valid('{"x":35}') → 1
json_valid('{"x":35') → 0

json_quote()函数

json_quote(X)函数将SQL值X(一个数字或一个字符串)转换为其相应的JSON表示。

如果X已经是格式正确的JSON,那么这个函数就没有作用。

json_quote(3.14159) → 3.14159
json_quote('verdant') → '"verdant"'
json_quote('[1]') → '"[1]"'
json_quote(json('[1]')) → '[1]'
json_quote('[1,') → '"[1"'

json_group_array()和json_group_object()聚合SQL函数

json_group_array（X）函数是一个聚合SQL函数，该函数返回一个由聚合中所有X值组成的JSON数组。

同样，json_group_object（NAME，VALUE）函数返回由聚合中的所有NAME / VALUE对组成的JSON对象。

json_each()和json_tree()的表值函数

json_each(X) 和 json_tree(X)表值函数遍历作为其第一个参数提供的 JSON 值，并为每个元素返回一行。

json_each(X) 函数仅遍历顶级数组或对象的直接子元素，或者如果顶级元素是原始值，则仅遍历顶级元素本身。json_tree(X) 函数从顶级元素开始递归遍历 JSON 子结构。

json_each(X,P)和json_tree(X,P)函数的工作原理和它们的单参数对应函数一样,只是它们将路径P所标识的元素视为顶层元素。

总结

如果是小应用sqlite还是不错的选择，现在还支持了JSON，很Nice！