PostgreSQL如何提取JSON字段指定键值并拼接为目标查询结果

用到的核心JSON操作符/函数 #

• ->：JSON取值操作符，入参为键名，返回对应子节点的JSON类型值，用于逐层定位嵌套JSON结构
• ->>：JSON取值操作符，入参为键名，返回对应子节点的文本类型值，用于直接获取最终的字段值做后续计算/拼接
• concat_ws()：通用字符串拼接函数，指定分隔符后可拼接多个字段值，对null值容错性更好

实现语句 #

假设存储JSON数据的字段名为json_col，所属表名为your_table，语句如下：

SELECT
  CONCAT_WS(
    ' ',
    -- 定位到time节点后取出increments的值，自动转文本
    json_col->'time'->>'increments',
    -- 定位到time节点后取出increment_type的值
    json_col->'time'->>'increment_type'
  ) AS Column_a
FROM your_table;

针对你给出的样例JSON数据，上述语句执行后Column_a的返回值就是 5 weeks，完全匹配需求。

补充说明 #

• 上述写法同时兼容json和jsonb两种字段类型，不需要调整语法
• 如果习惯用拼接符写法，也可以用||实现同等效果，语句如下：

SELECT
  (json_col->'time'->>'increments') || ' ' || (json_col->'time'->>'increment_type') AS Column_a
FROM your_table;

该写法的缺点是如果两个提取值任意一个为null，整个拼接结果会返回null，容错性弱于concat_ws()写法。

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