小. 快速. 可靠.
选择任意三个.
JSON1扩展

1. Overview

json1扩展是可加载的扩展 ,它实现了十五个应用程序定义的SQL函数和两个表值函数 ,这些函数对于管理存储在SQLite数据库中的JSON内容很有用. 有十三种标量函数:

  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_insert(json,path,value,...)
  6. json_object(label1,value1,...)
  7. json_patch(json1,json2)
  8. json_remove(json,path,...)
  9. json_replace(json,path,value,...)
  10. json_set(json,path,value,...)
  11. json_type(json)
    json_type(json,path)
  12. json_valid(json)
  13. 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)

2. Compiling the JSON1 Extension

可加载扩展文档描述了如何将可加载扩展编译为共享库. 那里描述的技术对于json1模块工作正常.

json1源代码包含在SQLite 合并中 ,但默认情况下已禁用. 添加-DSQLITE_ENABLE_JSON1编译时选项,以启用内置于amalgamation中的json1扩展. 构建命令行外壳程序时,标准的makefile包含-DSQLITE_ENABLE_JSON1以及一些测试实用程序,因此此扩展名通常在命令行外壳程序中可用.

3. Interface Overview

json1扩展名(当前)将JSON存储为普通文本.

向后兼容性约束意味着SQLite仅能存储NULL,整数,浮点数,文本和BLOB的值. 无法添加第六种" JSON"类型.

json1扩展名(当前)不支持JSON的二进制编码. 实验无法找到比纯文本编码小得多或快得多的二进制编码. (当前实现以超过1 GB / s的速度解析JSON文本.)如果所有json1函数的任何参数为BLOB,则当前都会引发错误,因为BLOB保留用于将来的增强,其中BLOB将存储JSON的二进制编码.

json1扩展名末尾的" 1"是故意的. 设计人员预计,从json1汲取的经验教训,将来会出现不兼容的JSON扩展. 一旦获得足够的经验,就可以将某种JSON扩展折叠到SQLite核心中. 目前,JSON支持仍然是扩展.

3.1. JSON arguments

对于将JSON作为第一个参数的函数,该参数可以是JSON对象,数组,数字,字符串或null. SQLite数字值和NULL值分别解释为JSON数字和null. SQLite文本值可以理解为JSON对象,数组或字符串. 如果将不是格式正确的JSON对象,数组或字符串的SQLite文本值传递给json1函数,则该函数通常会引发错误. (此规则的例外是json_valid()json_quote() .)

为了确定有效性,将忽略JSON输入上的前导和尾随空格. 根据JSON规范,内部空格也将被忽略. 这些例程完全接受rfc-7159 JSON语法 -不多也不少.

3.2. PATH arguments

对于接受PATH参数的函数,该PATH必须格式正确,否则该函数将引发错误. " or "[ ]". A-形成阱PATH是与恰好一个"$"字符后面" 或的零个或更多个实例开始的文本值.

is usually a non-negative integer . 通常是一个非负整数 -th element of the array, starting with zero on the left. 在这种情况下,所选的数组元素是数组的第个元素,从左侧的零开始. can also be of the form " #- " in which case the element selected is the -th from the right. 也可以采用" # "的形式,在这种情况下,所选元素是从右起第个元素. 数组的最后一个元素是" #-1 ". 将"#"字符视为"数组中的元素数". 然后,表达式"#-1"求值为与数组中最后一个条目相对应的整数.

3.3. VALUE arguments

" arguments (also shown as " " and " "), those arguments is usually understood to be a literal strings that are quoted and becomes JSON string values in the result. 对于接受" "参数(也显示为" "和" ")的函数,这些参数通常被理解为带引号的文字字符串,并在结果中成为JSON字符串值. strings look like well-formed JSON, they are still interpreted as literal strings in the result. 即使输入字符串看起来像格式正确的JSON,它们仍然会在结果中解释为文字字符串.

但是,如果参数直接来自另一个json1函数的结果,则该参数应理解为实际JSON,并且将插入完整的JSON而不是带引号的字符串.

argument looks like a well-formed JSON array. 例如,在以下对json_object()的调用中, 参数看起来像一个格式良好的JSON数组. 但是,由于它只是普通的SQL文本,因此将其解释为文字字符串,并以带引号的字符串添加到结果中:

但是,如果外部json_object()调用中的参数是另一个json1函数(如json()json_array())的结果 ,则该值应理解为实际的JSON并按以下方式插入:

" arguments are always interpreted as JSON regardless of where the value for that argument comes from. 需要明确的是:" "参数始终被解释为JSON,无论该参数的值来自何处. " arguments are only interpreted as JSON if those arguments come directly from another json1 function. 但是,仅当" "参数直接来自另一个json1函数时,这些参数才被解释为JSON.

3.4. Compatibility

json1扩展使用SQLite 3.9.0(2015-10-14)引入的sqlite3_value_subtype()sqlite3_result_subtype()接口json1扩展在SQLite的早期版本中不起作用.

该JSON库的当前实现使用递归下降解析器. 为了避免使用过多的堆栈空间,具有超过2000个嵌套级别的任何JSON输入都被视为无效. RFC-7159第9节允许对JSON的兼容实现进行嵌套深度限制.

4. Function Details

以下各节提供了有关json1扩展中各种功能的操作的更多详细信息.

4.1. The json() function

json(X)函数验证其参数X是有效的JSON字符串,并返回该JSON字符串的精简版本(已删除所有不必要的空格). 如果X不是格式正确的JSON字符串,则此例程将引发错误.

如果json(X)的参数X包含带有重复标签的JSON对象,则是否保留重复项是不确定的. 当前实现保留重复项. 但是,此例程的将来增强功能可能会选择静默删除重复项.

Example:

4.2. The json_array() function

json_array()SQL函数接受零个或多个参数,并返回由这些参数组成的格式良好的JSON数组. 如果json_array()的任何参数是BLOB,则将引发错误.

SQL类型为TEXT的参数通常会转换为带引号的JSON字符串. 但是,如果参数是另一个json1函数的输出,则它将存储为JSON. 这允许嵌套对json_array()和json_object()的调用. json()函数还可用于强制将字符串识别为JSON.

Examples:

4.3. The json_array_length() function

json_array_length(X)函数返回JSON数组X中的元素数,如果X是除数组之外的某种JSON值,则返回0. json_array_length(X,P)将数组定位在X内的路径P处,并返回该数组的长度;如果路径P定位了除JSON数组以外的元素或X,则返回0;如果路径P未定位其中的任何元素,则返回NULL. X.如果X格式不正确的JSON或P格式不正确的路径,则会引发错误.

Examples:

4.4. The json_extract() function

json_extract(X,P1,P2,...)从X处格式良好的JSON提取并返回一个或多个值.如果仅提供单个路径P1,则结果的SQL数据类型对于JSON为NULL null,INTEGER或REAL表示JSON数值,INTEGER零表示JSON假值,INTEGER表示JSON真值,引号表示JSON字符串值以及JSON对象和数组值的文本表示形式. 如果存在多个路径参数(P1,P2等),则此例程将返回SQLite文本,该文本是格式正确的JSON数组,其中包含各种值.

Examples:

4.5. The json_insert(), json_replace, and json_set() functions

json_insert(),json_replace和json_set()函数都将单个JSON值作为其第一个参数,然后添加零对或更多对路径和值对,并返回一个新的JSON字符串,该字符串由path /更新输入的JSON形成值对. 这些功能的不同之处仅在于它们处理新值和覆盖现有值的方式.

Function覆盖是否已经存在?创建是否不存在?
json_insert()NoYes
json_replace()YesNo
json_set()YesYes

json_insert(),json_replace()和json_set()函数始终使用奇数个参数. 第一个参数始终是要编辑的原始JSON. 后续参数成对出现,每对中的第一个元素是路径,第二个元素是要在该路径上插入,替换或设置的值.

编辑从左到右顺序进行. 先前编辑引起的更改可能会影响后续编辑的路径搜索.

如果路径/值对的值是SQLite TEXT值,则通常将其作为带引号的JSON字符串插入,即使该字符串看起来像有效的JSON. 但是,如果该值是另一个json1函数(例如json()json_array()json_object() )的结果,则将其解释为JSON并插入为JSON,以保留其所有子结构.

如果第一个JSON参数的格式不正确,或者任何PATH参数的格式不正确,或者任何参数是BLOB,则这些例程将引发错误.

要使用数组索引为"#"的json_insert()将元素附加到数组的末尾. 例子:

其他例子:

4.6. The json_object() function

json_object()SQL函数接受零对或更多对参数,并返回由这些参数组成的格式正确的JSON对象. 每对的第一个参数是标签,每对的第二个参数是值. 如果json_object()的任何参数是BLOB,则将引发错误.

json_object()函数当前允许重复的标签而不会产生投诉,尽管在将来的增强中可能会更改.

SQL类型为TEXT的参数通常将转换为带引号的JSON字符串,即使输入文本为格式正确的JSON. 但是,如果参数是另一个json1函数的直接结果,则将其视为JSON,并保留其所有JSON类型信息和子结构. 这样可以嵌套对json_object()和json_array()的调用. json()函数还可用于强制将字符串识别为JSON.

Examples:

4.7. The json_patch() function

json_patch(T,P)SQL函数运行RFC-7396 MergePatch算法,将补丁P应用于输入T.返回T的补丁副本.

MergePatch可以添加,修改或删除JSON对象的元素,因此对于JSON对象,json_patch()例程是json_set()json_remove()的通用替代. 但是,MergePatch将JSON数组对象视为原子对象. MergePatch不能追加到数组,也不能修改数组的单个元素. 它只能将整个阵列作为单个单元插入,替换或删除. 因此,当处理包含数组(特别是具有大量子结构的数组)的JSON时,json_patch()并不那么有用.

Examples:

4.8. The json_remove() function

json_remove(X,P,...)函数将单个JSON值作为其第一个参数,后跟零个或多个路径参数. json_remove(X,P,...)函数返回一个新的JSON值,它是X,其中删除了由路径参数标识的所有元素. 选择X中找不到的元素的路径将被忽略.

删除顺序是从左到右. 由先前删除引起的更改可能会影响后续参数的路径搜索.

如果在不使用路径参数的情况下调用json_remove(X)函数,则它将返回重新格式化的输入X,并删除了多余的空格.

如果第一个参数格式不正确的JSON或后面的参数格式都不正确,或者任何参数是BLOB,则json_remove()函数将引发错误.

Examples:

4.9. The json_type() function

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()函数的任何参数的格式不正确或为BLOB,则将引发错误.

Examples:

4.10. The json_valid() function

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

Examples:

4.11. The json_quote() function

json_quote(X)函数将SQL值X(数字或字符串)转换为其相应的JSON表示形式.

Examples:

4.12. The json_group_array() and json_group_object() aggregate SQL functions

json_group_array(X)函数是一个聚合SQL函数 ,该函数返回一个由聚合中所有X值组成的JSON数组. 同样,json_group_object(NAME,VALUE)函数返回一个由聚合中的所有NAME / VALUE对组成的JSON对象.

4.13. The json_each() and json_tree() table-valued functions

表值函数 json_each(X)和json_tree(X)遍历作为其第一个参数提供的JSON值,并为每个元素返回一行. json_each(X)函数仅遍历顶级数组或对象的直接子级,或者仅遍历顶级元素本身(如果顶级元素是原始值). json_tree(X)函数以递归的方式遍历从顶层元素开始的JSON子结构.

json_each(X,P)和json_tree(X,P)函数的工作方式与它们的一元参数对应项相同,不同之处在于它们将路径P标识的元素视为顶级元素.

json_each()和json_tree()返回的表的架构如下:

CREATE TABLE json_tree(
    key ANY,             -- key for current element relative to its parent
    value ANY,           -- value for the current element
    type TEXT,           -- 'object','array','string','integer', etc.
    atom ANY,            -- value for primitive types, null for array & object
    id INTEGER,          -- integer ID for this element
    parent INTEGER,      -- integer ID for the parent of this element
    fullkey TEXT,        -- full path describing the current element
    path TEXT,           -- path to the container of the current row
    json JSON HIDDEN,    -- 1st input parameter: the raw JSON
    root TEXT HIDDEN     -- 2nd input parameter: the PATH at which to start
);

"键"列是JSON数组元素的整数数组索引和JSON对象元素的文本标签. 在所有其他情况下,键列均为NULL.

" atom"列是与原始元素(JSON数组和对象以外的元素)相对应的SQL值. 对于JSON数组或对象," atom"列为NULL. "值"列与原始JSON元素的"原子"列相同,但采用数组和对象的文本JSON值.

"类型"(type)列是根据以下类型从(null)," true"," false"," integer"," real"," text"," array"," object")获取的SQL文本值.当前的JSON元素.

" id"列是一个整数,用于标识完整JSON字符串中的特定JSON元素. " id"整数是一个内部管理编号,其计算可能会在将来的版本中更改. 唯一的保证是每一行的" id"列都会不同.

对于json_each(),"父"列始终为NULL. 对于json_tree(),"父"列是当前元素的父元素的" id"整数,或者是顶级JSON元素或第二个参数中由根路径标识的元素的NULL.

" fullkey"列是一个文本路径,用于唯一标识原始JSON字符串中的当前行元素. 即使" root"参数提供了替代的起点,也将返回真正的顶级元素的完整键.

"路径"列是保存当前行的数组或对象容器的路径,或者是在迭代从原始类型开始并因此仅提供单行输出的情况下的当前行的路径.

4.13.1. Examples using json_each() and json_tree()

假设表" CREATE TABLE user(name,phone)"在user.phone字段中存储零个或多个电话号码作为JSON数组对象. 要查找所有具有704区号的电话号码的用户,请执行以下操作:

SELECT DISTINCT user.name
  FROM user, json_each(user.phone)
 WHERE json_each.value LIKE '704-%';

现在假设如果用户只有一个电话号码,则user.phone字段包含纯文本,如果用户有多个电话号码,则包含JSON数组. 提出了相同的问题:"哪个用户的704区号有电话号码?" 但是现在,只有具有两个或多个电话号码的用户才能调用json_each()函数,因为json_each()需要格式正确的JSON作为其第一个参数:

SELECT name FROM user WHERE phone LIKE '704-%'
UNION
SELECT user.name
  FROM user, json_each(user.phone)
 WHERE json_valid(user.phone)
   AND json_each.value LIKE '704-%';

考虑带有" CREATE TABLE big(json JSON)"的其他数据库. 要查看数据的逐行完整分解,请执行以下操作:

SELECT big.rowid, fullkey, value
  FROM big, json_tree(big.json)
 WHERE json_tree.type NOT IN ('object','array');

在上一个版本中,WHERE子句的"类型NOT IN('object','array')类型"一词禁止使用容器,只允许通过叶子元素. 这样可以达到相同的效果:

SELECT big.rowid, fullkey, atom
  FROM big, json_tree(big.json)
 WHERE atom IS NOT NULL;

假设BIG表中的每个条目都是一个JSON对象,其中的" $ .id"字段是唯一的标识符,而" $ .partlist"字段可以是一个深层嵌套的对象. 您想在其" $ .partlist"中的任何位置查找包含对uuid'6fa5181e-5721-11e5-a04e-57f3d7b32808'的一个或多个引用的每个条目的ID.

SELECT DISTINCT json_extract(big.json,'$.id')
  FROM big, json_tree(big.json, '$.partlist')
 WHERE json_tree.key='uuid'
   AND json_tree.value='6fa5181e-5721-11e5-a04e-57f3d7b32808';

by  ICOPY.SITE