我有一个数据col,其中包含一些记录作为JSON数组,示例
[
{
"id": 4065,
"pageTitle": "Lorem Ipsum",
"children": [
{
"id": 4067,
"pageTitle": "Foo",
},
{
"id": 4072,
"pageTitle": "Bar",
}
},
{
"id": 4070,
"pageTitle": "Another Lorem Ipsum",
"children": [
{
"id": 4068,
"pageTitle": "Another Foo",
},
{
"id": 4073,
"pageTitle": "Another Bar",
}
}
]
我下面的查询是获取所有父ID,而忽略了子ID
SELECT JSON_EXTRACT(data, "$[*].id")
FROM `my_table`;
// Returns only 4065 & 4067
即使是儿童元素或大孩子等,我如何获取和所有ID?
还可以将结果返回为单个索引数组,例如
[4065, 4067, 4072, 4070, 4068, 4073]
或需要以编程方式处理,例如PHP?
在MySQL 5.7中,JSON函数受到限制。但是,您可以向JSON_EXTRACT
提供多个途径,因此,如果您的桌子深度不超过2个级别(即孙子,但没有曾孙),则可以使用:
SELECT JSON_EXTRACT(data, "$[*].id", '$[*].children[*].id', '$[*].children[*].children[*].id')
FROM my_table
dbfiddle上的演示
不是mySQL答案,如果您的系统上有jq
,则可以使用此脚本
jq '[..|.id?]' file
这将遍历整个JSON数据文件(无论它有多深),如果找到id
密钥,它会打印其关联的值。
jqplay上的演示
这是一个示例查询,选择所有孙子都具有" value_to_find"的值。在称为" JSON_COLUMN"的JSON列中:
SELECT *
FROM your_table
WHERE json_column ->> '$**.grandchild_name' = 'value_to_find'
上面查询中使用的'$ **'语法都会选择到孙子的所有可能路径,无论父母的名字如何。这使查询可以找到孙子,而不论父母名称。