PostgreSQL中使用json_build_object查询JSON列时忽略命名空间与大小写的实现方案

针对你遇到的固件升级前后JSON属性格式不一致的问题，我们可以通过两种方式实现单一查询兼容两种场景：自定义函数封装匹配逻辑（更简洁，适合多属性场景），或者直接在查询中使用COALESCE结合键匹配（无需创建函数，适合少量属性）。

方法一：创建自定义函数（推荐） #

这个函数会自动处理命名空间前缀和大小写问题，你只需要传入JSON数据和目标属性的原始名称（全小写即可），它会返回匹配到的值。

首先创建函数：

CREATE OR REPLACE FUNCTION extract_json_property(json_data jsonb, target_key text)
RETURNS jsonb AS $$
BEGIN
  -- 遍历JSON的所有键，去掉命名空间前缀后转小写，与目标键的小写匹配
  RETURN (
    SELECT value 
    FROM jsonb_each(json_data) 
    WHERE lower(replace(key, 'dsns:', '')) = lower(target_key)
    LIMIT 1
  );
END;
$$ LANGUAGE plpgsql STABLE;

函数说明 #

• 我们先将JSON转为jsonb类型（比原生json操作更高效），然后用jsonb_each展开所有键值对
• 通过replace(key, 'dsns:', '')去掉命名空间前缀，再用lower()统一转为小写，和目标键的小写形式匹配
• LIMIT 1确保只返回第一个匹配到的值（符合你"属性含义与数量未变"的前提）

使用函数的查询语句 #

SELECT json_build_object(
    'requestedproperty', extract_json_property("data"::jsonb, 'requestedproperty'),
    'anotherrequestedproperty', extract_json_property("data"::jsonb, 'anotherrequestedproperty')
) AS "data"
FROM device_data
WHERE id = 'f4ddf01fcb6f322f5f118100ea9a81432'
  AND timestamp >= '2020-01-01 08:36:59.698'
  AND timestamp <= '2022-02-16 08:36:59.698'
ORDER BY timestamp DESC;

如果你的data列已经是jsonb类型，可以去掉::jsonb转换，进一步提升性能。

方法二：直接在查询中使用COALESCE（无需函数） #

如果只是少量属性需要提取，也可以直接用COALESCE依次尝试不同格式的键，返回第一个非空的值：

SELECT json_build_object(
    'requestedproperty', 
    COALESCE(
        -- 先尝试原始键
        "data"->'requestedproperty',
        -- 再尝试带命名空间的键
        "data"->'dsns:requestedproperty',
        -- 最后尝试驼峰式键
        "data"->'requestedProperty',
        -- 兜底：通过展开匹配所有变体
        (SELECT value FROM jsonb_each("data"::jsonb) 
         WHERE lower(replace(key, 'dsns:', '')) = 'requestedproperty'
         LIMIT 1)
    ),
    'anotherrequestedproperty',
    COALESCE(
        "data"->'anotherrequestedproperty',
        "data"->'dsns:anotherrequestedproperty',
        "data"->'anotherRequestedProperty',
        (SELECT value FROM jsonb_each("data"::jsonb) 
         WHERE lower(replace(key, 'dsns:', '')) = 'anotherrequestedproperty'
         LIMIT 1)
    )
) AS "data"
FROM device_data
WHERE id = 'f4ddf01fcb6f322f5f118100ea9a81432'
  AND timestamp >= '2020-01-01 08:36:59.698'
  AND timestamp <= '2022-02-16 08:36:59.698'
ORDER BY timestamp DESC;

额外优化建议 #

如果你的data列目前是json类型，建议转为jsonb类型，因为jsonb对键值操作的性能更好，尤其适合这种需要遍历、匹配键的场景：

ALTER TABLE device_data ALTER COLUMN data TYPE jsonb USING data::jsonb;

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