[SQL]一条SQL解析JSON数据插入到另外一张表

记录下现阶段的项目有处理旧数据的需求，由于旧数据的文件数据都存在JSON里面。 需要从JSON里面解析出来插入到另外一张表里。 旧数据的JSON内容为

[
    {
        "name":"文件说明",
        "fileName":[
            {
                "id":50724,
                "fileName":"文件说明-202205261654569555220.pdf",
                "original":"文件说明-20220526.pdf",
                "type":"pdf",
                "fileSize":"460659",
                "createUser":1714,
                "createTime":null,
                "fileDate":null,
                "delFlag":null,
                "fileOuterChain":null,
                "fileTag":0
            }
        ],
        "fileVersionNumber":"不适用",
        "fileDate":"不适用"
    }
]

新表的结构为

CREATE TABLE `project_file` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `project_id` bigint DEFAULT NULL,
  `tr_id` bigint DEFAULT NULL,
  `name` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `file_id` int DEFAULT NULL,
  `file_name` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `file_alias` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `file_version_number` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `file_date` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

由于 file_json 可能包含多条文件数据，可以使用MySQL内置函数JSON_TABLE来解析JSON数组，将每个文件数据拆分为单独的行，然后将每行数据插入到project_file表中。以下是解决方案：

INSERT INTO project_file (`name`,file_name, file_alias, project_id, file_id, tr_id, file_version_number, file_date)
SELECT
  `name`,
  file_name,
  file_alias,
  project_id,
  file_id,
  tr_id,
  file_version_number, 
  file_date
FROM file_revise,
JSON_TABLE(file_json, '$[*]' COLUMNS (
    `name` VARCHAR(255) PATH '$.name',
    file_name VARCHAR(255) PATH '$.fileName[0].fileName',
    file_alias VARCHAR(255) PATH '$.fileName[0].original',
    file_id BIGINT PATH '$.fileName[0].id',
    `file_version_number` VARCHAR(255) PATH '$.fileVersionNumber',
    `file_date` VARCHAR(255) PATH '$.fileDate'
)) AS file_data
WHERE file_data.file_name IS NOT NULL AND file_data.file_id IS NOT NULL AND file_data.file_alias IS NOT NULL;