SQLite json_tree() 函数使用指南

SQLite json_tree() 函数递归遍历指定 JSON 文档,为每个子元素生成一行(包括参数本身),最终返回由所有的行组成的结果集。

SQLite json_tree() 函数是一个表值函数,类似的函数有: json_each()

json_each() 只遍历 JSON 对象或者数组的直接子元素;而 json_tree() 则递归遍历所有子元素。

json_tree() 语法

这里是 SQLite json_tree() 的语法:

json_tree(json, path)

参数

json
必需的。一个 JSON 文档。
path
可选的。一个路径表达式。

返回值

SQLite json_tree() 函数返回一个具有以下列的结果集:

key
如果参数为 JSON 数组,则 key 列为数组的元素的索引;如果参数为 JSON 对象,则 key 列为对象的成员名称;其他情况, key 列为 NULL
value
当前元素的值。
type
当前元素的 JSON 类型。可能的值: 'null''true''false''integer''real''text''array''object'。 他们与 json_type() 函数相同。
atom
如果 value 是 JSON 原始类型,atom 存储其对应的 SQL 值;否则该列为 NULL
id
标识此行唯一性的一个整数。
parent
父元素的整数 ID。
fullkey
它是当前行元素的路径。
path
当前行元素的父元素的路径。

json_tree() 示例

这里列出了几个常见的 json_tree() 用法示例。

示例: 数组

在这个示例中,我们使用 json_tree() 函数递归遍历一个 JSON 数组中的元素:

SELECT * FROM json_tree('[1, 2, 3]');
key  value    type     atom  id  parent  fullkey  path
---  -------  -------  ----  --  ------  -------  ----
     [1,2,3]  array          0           $        $
0    1        integer  1     1   0       $[0]     $
1    2        integer  2     2   0       $[1]     $
2    3        integer  3     3   0       $[2]     $

这里,参数本身也作为一个行出现在结果集中,它的 keyNULL。 而子元素的 parent 列的值为其父元素的 ID。

示例: 多维数组

在这个示例中,我们使用 json_tree() 函数递归遍历一个 JSON 数组中的元素:

SELECT * FROM json_tree('[1, 2, [3, [4, 5]]]');
key  value            type     atom  id  parent  fullkey     path
---  ---------------  -------  ----  --  ------  ----------  -------
     [1,2,[3,[4,5]]]  array          0           $           $
0    1                integer  1     1   0       $[0]        $
1    2                integer  2     2   0       $[1]        $
2    [3,[4,5]]        array          3   0       $[2]        $
0    3                integer  3     4   3       $[2][0]     $[2]
1    [4,5]            array          5   3       $[2][1]     $[2]
0    4                integer  4     6   5       $[2][1][0]  $[2][1]
1    5                integer  5     7   5       $[2][1][1]  $[2][1]

示例: 对象

在这个示例中,我们使用 json_tree() 函数递归遍历一个 JSON 对象中的元素:

SELECT * FROM json_tree('{"x": 1, "y": {"a": 3, "b": true}}');
key  value                         type     atom  id  parent  fullkey  path
---  ----------------------------  -------  ----  --  ------  -------  ----
     {"x":1,"y":{"a":3,"b":true}}  object         0           $        $
x    1                             integer  1     2   0       $.x      $
y    {"a":3,"b":true}              object         4   0       $.y      $
a    3                             integer  3     6   4       $.y.a    $.y
b    1                             true     1     8   4       $.y.b    $.y

示例:指定路径

在这个示例中,我们使用 json_tree() 函数递归遍历一个 JSON 对象中的通过路径指定的元素:

SELECT * FROM json_tree('{"x": 1, "y": {"a": 3, "b": true}}', '$.y');
key  value             type     atom  id  parent  fullkey  path
---  ----------------  -------  ----  --  ------  -------  ----
y    {"a":3,"b":true}  object         4           $.y      $
a    3                 integer  3     6   4       $.y.a    $.y
b    1                 true     1     8   4       $.y.b    $.y