如何基于给定JSON数据编写MySQL查询语句创建对应视图

以下是适配MySQL 8.0及以上版本的视图创建语句，可直接解析给定的JSON数组生成结构化可查询视图：

CREATE OR REPLACE VIEW v_device_info AS
SELECT
    -- 布尔类型可按需转为TINYINT，示例保留JSON提取的原始值
    JSON_UNQUOTE(JSON_EXTRACT(device_item, '$.available')) AS available,
    JSON_UNQUOTE(JSON_EXTRACT(device_item, '$.platform')) AS platform,
    JSON_UNQUOTE(JSON_EXTRACT(device_item, '$.version')) AS os_version,
    JSON_UNQUOTE(JSON_EXTRACT(device_item, '$.uuid')) AS device_uuid,
    JSON_UNQUOTE(JSON_EXTRACT(device_item, '$.cordova')) AS cordova_version,
    JSON_UNQUOTE(JSON_EXTRACT(device_item, '$.model')) AS device_model,
    JSON_UNQUOTE(JSON_EXTRACT(device_item, '$.manufacturer')) AS manufacturer,
    JSON_UNQUOTE(JSON_EXTRACT(device_item, '$.isVirtual')) AS is_virtual_device,
    JSON_UNQUOTE(JSON_EXTRACT(device_item, '$.serial')) AS device_serial,
    -- 时间类型可按需转为DATETIME，示例保留字符串格式
    JSON_UNQUOTE(JSON_EXTRACT(device_item, '$.access_time')) AS access_time,
    JSON_UNQUOTE(JSON_EXTRACT(device_item, '$.installed_app_version')) AS app_version,
    JSON_UNQUOTE(JSON_EXTRACT(device_item, '$.logout_time')) AS logout_time
FROM
    JSON_TABLE(
        -- 若JSON存储在业务表字段中，此处替换为对应表.字段名即可
        '[{\"available\":true,\"platform\":\"iOS\",\"version\":\"14.7\",\"uuid\":\"4B9DEAA2-E2C0-4373-808D-96072BF070C6\",\"cordova\":\"5.1.1\",\"model\":\"iPad7,3\",\"manufacturer\":\"Apple\",\"isVirtual\":false,\"serial\":\"unknown\",\"access_time\":\"2021-08-15 15:51:50\",\"installed_app_version\":\"0.0.1\",\"logout_time\":\"2021-08-16 07:18:47\"},{\"available\":true,\"platform\":\"Android\",\"version\":\"10\",\"uuid\":\"822e2a0a98113125\",\"cordova\":\"7.1.4\",\"model\":\"MI 9\",\"manufacturer\":\"Xiaomi\",\"isVirtual\":false,\"serial\":\"unknown\",\"access_time\":\"2021-08-16 06:16:57\",\"installed_app_version\":\"0.0.1\"},{\"available\":true,\"platform\":\"Android\",\"version\":\"9\",\"uuid\":\"32da96d832075053\",\"cordova\":\"7.1.4\",\"model\":\"Redmi Note 6 Pro\",\"manufacturer\":\"Xiaomi\",\"isVirtual\":false,\"serial\":\"unknown\",\"access_time\":\"2021-08-16 06:21:55\",\"installed_app_version\":\"0.0.1\"}]',
        '$[*]' COLUMNS (
            device_item JSON PATH '$'
        )
    ) AS parsed_device_list;

使用说明 #

• 依赖MySQL 8.0及以上版本提供的JSON_TABLE函数实现JSON数组拆分为多行，低于该版本的MySQL需升级版本或使用自定义存储过程实现JSON数组拆分。
• 若你的JSON数组存储在业务表的字段中（比如表device_raw_data的raw_json字段），将JSON_TABLE的第一个参数替换为对应字段名，同时补充对应业务表的FROM关联逻辑即可。
• 可按需调整字段的类型转换逻辑：
  • 布尔字段转TINYINT：CAST(JSON_EXTRACT(device_item, '$.available') AS UNSIGNED) AS available
  • 时间字段转DATETIME：CAST(JSON_UNQUOTE(JSON_EXTRACT(device_item, '$.access_time')) AS DATETIME) AS access_time

内容的提问来源于stack exchange，提问作者Atiqur Rahman