json_each()和json_tree()表值函数 用法

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所标识的元素视为顶层元素。
json在这里插入代码片_each()和json_tree()返回的表的模式如下。

创建 表json_tree(
    key ANY,             -当前元素相对于其父元素的键
    value ANY,           -当前元素的值
    type TEXT,           -'object'，'array'，'string'，'integer'等。
    atom ANY,            -原始类型的值，数组和对象为null
    id INTEGER,          -此元素的整数ID
    parent INTEGER,      -此元素的父级的整数ID
    fullkey TEXT,        -描述当前元素的完整路径
    path TEXT,           -当前行容器的路径
    json JSON HIDDEN,    -第一个输入参数：原始JSON
    root TEXT HIDDEN     -第二个输入参数：开始的路径
);

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

"atom "列是对应于基元的SQL值–JSON数组和对象以外的元素。对于JSON数组或对象来说,"atom "列是NULL。"value "列与原始JSON元素的 "atom "列相同,但对于数组和对象来说,则采用文本JSON值。

"type "列是根据当前JSON元素的类型从(‘null’、‘true’、‘false’、‘integer’、‘real’、‘text’、‘array’、‘object’)中提取的SQL文本值。

"id "列是一个整数,用于标识完整JSON字符串中的一个特定JSON元素。id "整数是一个内部的内务管理数字,它的计算方法可能会在未来的版本中发生变化。唯一能保证的是,"id "列对每一行都是不同的。

对于json_each(),"parent "列总是NULL。对于json_tree(),"parent "列是当前元素的父元素的 "id "整数,对于顶层JSON元素或第二个参数中根路径标识的元素则为NULL。

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

"路径 "列是指向持有当前行的数组或对象容器的路径,或者在迭代开始于基元类型的情况下,指向当前行的路径,因此只提供单行输出。

使用json_each()和json_tree()的例子。

假设表 "CREATE TABLE user(name,phone)"在user.phone字段中以JSON数组对象的形式存储了0个或多个电话号码。要找到所有拥有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子句中的 "type 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’;