处理MYSQL里json格式结构化

Adore_rui

已于 2023-09-08 10:52:55 修改

阅读量122

点赞数

文章标签： mysql json 前端

于 2023-09-04 13:42:18 首次发布

本文链接：https://blog.csdn.net/Adore_rui/article/details/132667488

版权

一、建新数据库

二、建表

三、插入数据

昨天写的好像没存下来，那只能重新写一遍了

背景：原始数据库是mysql的里面一个字段为json，所以存了半结构化的无数据

目标：处理成结构化数据，存到dwd层

一、建新数据库

CREATE database if not exists an hetong test;

二、建表

USE anneng_test;
drop table hr_department;
CREATE TABLE hr_department(
    id INT(10) COMMENT '主键',
    name VARCHAR(25) DEFAULT NULL COMMENT '单位名称',
    deleted BOOL,
    sortnum VARCHAR(30) DEFAULT NULL COMMENT '排序号',
    disabled BOOL DEFAULT NULL,
    parentId VARCHAR(10) DEFAULT NULL COMMENT '上级单位id',
    postCode VARCHAR(10) DEFAULT NULL,
    updatedAt VARCHAR(35) DEFAULT NULL,
    postalAddress VARCHAR(35) DEFAULT NULL,
    registeredAddress VARCHAR(35) DEFAULT NULL,
    uniformSocialCreditCode VARCHAR(35) DEFAULT NULL
);

三、插入数据

导入数据这里有个插曲，有的字段由于mysql版本的问题不兼容，所以需要修改字段类型

ALTER TABLE hr_department
MODIFY COLUMN deleted BOOL;

在插入

USE an_feb_ods_hetong;  -- 切换到目标数据库
-- 插入解耦后的数据到新表格
INSERT INTO hr_department(
    id,
    parentId,
    json_id,
    name,
    deleted,
    sortnum,
    disabled,
    json_parentId,
    postCode,
    updatedAt,
    postalAddress,
    registeredAddress,
    uniformSocialCreditCode
)
SELECT
    id,
    parentId,
    JSON_UNQUOTE(JSON_EXTRACT(json, '$.id')) AS json_id,
    JSON_UNQUOTE(JSON_EXTRACT(json, '$.name')) AS name,
    JSON_UNQUOTE(JSON_EXTRACT(json, '$.deleted')) AS deleted,
    JSON_UNQUOTE(JSON_EXTRACT(json, '$.sortnum')) AS sortnum,
    JSON_UNQUOTE(JSON_EXTRACT(json, '$.disabled')) AS disabled,
    JSON_UNQUOTE(JSON_EXTRACT(json, '$.parentId')) AS json_parentId,
    JSON_UNQUOTE(JSON_EXTRACT(json, '$.postCode')) AS postCode,
    JSON_UNQUOTE(JSON_EXTRACT(json, '$.updatedAt')) AS updatedAt,
    JSON_UNQUOTE(JSON_EXTRACT(json, '$.postalAddress')) AS postalAddress,
    JSON_UNQUOTE(JSON_EXTRACT(json, '$.registeredAddress')) AS registeredAddress,
    JSON_UNQUOTE(JSON_EXTRACT(json, '$.uniformSocialCreditCode')) AS uniformSocialCreditCode
FROM an_hetong.hr_department;