简介

在 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 是一个文本值,它正好以一个 ‘$’ 字符开头,后跟零个或多个".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]', '$')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}]}', '$')  '{"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}]}' -> '$'  '{"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}','$')  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”]}''$')→'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!