我有一个JSON,它是通过Airbyte从API查询中带入MariaDB/MYSQL数据库的。我使用JSON_TABLE将JSON分成列-这 * 大部分 * 工作得很好,但我只是注意到有一个JSON元素数组会不时变化。他们发送的不是唯一的密钥对,而是通用的密钥对,例如:
{
"id": 123,
"position": "North",
"attributes": [{
"name": "First Name",
"value": "Santa"
}, {
"name": "Last Name",
"value": "Claus"
}]
}
字符串
这些元素可以以任何顺序出现,所以我不能用JSON_TABLE查询来定位它们:
SELECT t.id, t.position, t.firstName, t.lastName
FROM
(
mytable
JOIN JSON_TABLE(mytable, '$'
COLUMNS (
id int(10) PATH '$.id',
position varchar(20) PATH '$.position',
firstName varchar(20) PATH '$.attributes[0].value',
lastName varchar(20) PATH '$.attributes[1].value'
)
) t
)
型
如何处理以动态顺序返回的属性?
此外,元素的数量也可能发生变化,因此,如果元素不存在,无论我使用什么来定位元素,都必须优雅地失败。
我的计划是使用JSON_TABLE,所以搜索对象name =“First Name”,然后将值返回给元素。我不知道如何在JSON_TABLE查询中做到这一点。我试过:
First Name varchar(20) PATH REPLACE(JSON_SEARCH('$.attributes', 'all', 'First Name'), '.name', '.value'))
型
在标准查询中,它将返回元素路径。然而,我需要动态构建一个JSON_TABLE才能正常工作。我在REPLACE(JSON_TABLE)附近得到一个SQL错误,我没有一个工作示例来比较。
1条答案
按热度按时间jpfvwuh41#
我们的想法是使用
json_table
将$.attributes
数组转换为行。您可以使用嵌套的path子句,select内部的子查询等。其余的是直接的:字符串
DB<>Fiddle