使用 SQLite 和 json_tree 函数从 JSON 对象中提取值

Question

我有一个表(命名为patrons)，其中包含一列(命名，json_patron_varfields)的JSON数据 - 一个看起来像这样的对象数组：

[
{
"display_order": 1,
"field_content": "example 1",
"name": "Note",
"occ_num": 0,
"varfield_type_code": "x"
},
{
"display_order": 2,
"field_content": "example 2",
"name": "Note",
"occ_num": 1,
"varfield_type_code": "x"
},
{
"display_order": 3,
"field_content": "some field we do not want",
"occ_num": 0,
"varfield_type_code": "z"
}
]

我正在尝试做的是针对包含名为varfield_type_code的键和x值的对象，我已经能够使用以下查询执行此操作：

SELECT
patrons.patron_record_id,
json_extract(patrons.json_patron_varfields, json_tree.path)
FROM
patrons,
json_tree(patrons.json_patron_varfields)
WHERE
json_tree.key = 'varfield_type_code'
AND json_tree.value = 'x'

我的问题是...如何从要提取的对象中提取(甚至可能筛选)field_content键的值？

我正在为如何做到这一点的语法而苦苦挣扎...我认为它可以像使用json_extract(patrons.json_patron_varfields, json_tree.path."field_content")一样简单，但这似乎不正确。

Answer 1

你可以连接来构建字符串

json_tree.path || '.field_content'

使用您给出的结构 - 您还可以使用json_each()而不是json_tree()这可能会简化事情。

提取：

SELECT
patrons.patron_record_id,
json_extract(value, '$.field_content')
FROM
patrons, 
json_each(patrons.json_patron_varfields)
WHERE json_extract(value, '$.varfield_type_code') = 'x'

滤波器：

SELECT
patrons.patron_record_id,
value
FROM
patrons, 
json_each(patrons.json_patron_varfields)
WHERE json_extract(value, '$.varfield_type_code') = 'x'
AND   json_extract(value, '$.field_content') = 'example 2'