将含参数的ID字段拆分至多列创建视图（split_part不可用）

针对这种参数顺序不固定的键值对拆分需求，可以利用字符串处理+JSON函数实现，具体步骤如下：

1. 拆分基础ID与参数字符串 #

先通过split_part提取冒号前的实际ID，同时分离出冒号后的参数字符串：

SELECT
  split_part(id, ':', 1) AS id,
  date,
  substring(id from ':(.*)') AS params_str
FROM 原表名;

2. 将参数字符串转为JSON键值对 #

把参数字符串按&拆分为单个参数项，再将每个参数拆成键值对，最终聚合为JSONB对象：

SELECT
  split_part(t.id, ':', 1) AS id,
  t.date,
  jsonb_object_agg(split_part(param, '=', 1), split_part(param, '=', 2)) AS params_json
FROM 原表名 t,
     unnest(string_to_array(substring(t.id from ':(.*)'), '&')) AS param
GROUP BY split_part(t.id, ':', 1), t.date;

3. 创建视图并提取目标字段 #

基于上述结果，直接从JSONB对象中提取需要的字段，不存在的参数会自动返回NULL：

CREATE VIEW 目标视图名 AS
SELECT
  base.id,
  base.date,
  base.params_json ->> 'type' AS type,
  base.params_json ->> 'quality' AS quality,
  base.params_json ->> 'country' AS country
FROM (
  SELECT
    split_part(t.id, ':', 1) AS id,
    t.date,
    jsonb_object_agg(split_part(param, '=', 1), split_part(param, '=', 2)) AS params_json
  FROM 原表名 t,
       unnest(string_to_array(substring(t.id from ':(.*)'), '&')) AS param
  GROUP BY split_part(t.id, ':', 1), t.date
) base;

补充说明 #

如果使用MySQL，可以用JSON_OBJECTAGG替代jsonb_object_agg，字符串分割逻辑可通过SUBSTRING_INDEX结合JSON_TABLE实现，核心思路一致，不依赖参数顺序。

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