PostgreSQL查询：如何从资产表自定义字段中提取指定字段值？

我有一个资产数据库，不同类型的资产对应一组独特的自定义字段。现有查询返回如下结果：

需要编写PostgreSQL查询，仅提取指定自定义字段（如Model）的值，生成如下格式的结果：

解决方案 #

方法1：使用正则表达式提取

直接通过substring函数配合正则表达式匹配Model字段的值：

SELECT
    asset_id,
    asset_type,
    substring(custom_fields FROM '"Model"="([^"]+)"') AS model
FROM
    your_table_name;

说明：正则表达式"Model"="([^"]+)"会精准匹配"Model"="开头的片段，捕获从下一个字符到下一个双引号之间的内容，也就是Model对应的值。

方法2：转换为JSON格式提取

先将非标准的键值对字符串转换为标准JSON，再用JSON函数提取目标字段：

SELECT
    asset_id,
    asset_type,
    (replace(replace(custom_fields, '"=', '":'), ',"', ',"'))::json ->> 'Model' AS model
FROM
    your_table_name;

说明：

1. 第一次replace把"key"="value"替换为"key":"value"，符合JSON键值对格式
2. 第二次replace确保逗号分隔的格式正确（原数据已满足，此步为兼容可能的异常格式）
3. 转换为JSON类型后，用->>操作符提取Model字段的文本值

两种方法都会在资产没有Model字段时返回NULL，符合业务预期。

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