mysql 8.0 抽取JSON中底层对象属性并转成行

本文链接：https://blog.csdn.net/Mo_if_peace/article/details/137508756

本文介绍了如何通过MySQL8.0的JSON_EXTRACT和JSON_TABLE函数，从数据库表的JSON字段中提取fileName和filePath，实现数据拉平并展示了一个示例SQL查询，用于获取特定code下的文件信息。

摘要由CSDN通过智能技术生成

需求：从数据库表中的json字段提取fileName，filePath，对象转成行，样例如下：

{
    "decorationBudgetDetails": [
        {
            "fileType": "jpg",
            "fileName": "文件1",
            "filePath": "https://192.168.2.198/d291047e5e5c43a6a8e2208841dd42c7.jpg"
        }
    ],
    "detailId": "0",
    "designSketch": [
        {
            "fileType": "jpg",
            "fileName": "文件2",
            "filePath": "https://192.168.2.198/6837a7ff2509444a9a2fe2a3916c8104.jpg"
        }
    ],
    "requestDocument": [
        {
            "fileType": "png",
            "fileName": "文件3",
            "filePath": "https://192.168.2.198/dbb46e14133d4c65b2ca02f416807763.png"
        }
    ]
}

思路：

1、通过mysql8.0虚拟表功能实现对底层对象抽取拉平；

2、从抽取对象中抽取所需字段。

样例SQL：

SELECT 
    JSON_UNQUOTE(JSON_EXTRACT(json_array_element, '$.fileName')) AS fileName,
    JSON_UNQUOTE(JSON_EXTRACT(json_array_element, '$.filePath')) AS filePath
FROM
    apply_detail,
    JSON_TABLE(
        ext_info,
        "$.*[*]" COLUMNS (
            json_array_element JSON PATH "$"
        )
    ) AS jt
WHERE fee_code = '202206000167'

其中 apply_detail为表名，ext_info为表中的目标json字段，jt为虚拟表， $.*[*] 是匹配所有底层数组中的对象。

效果展示：