Postgres如何从JSON格式字符串字段提取指定条件值生成新列

你的需求可以通过Postgres内置的JSON处理函数结合横向连接实现，具体操作如下：

注意：以下方案默认你的表名为your_table_name，请替换为实际使用的表名。

基础实现（单匹配项场景） #

因为Communication是字符串类型，首先需要通过to_jsonb()转换为JSONB格式，再拆分数组过滤条件取值：

SELECT 
  t.Name,
  t.FirstName,
  elem ->> 'adresse' AS Mobilenumber
FROM your_table_name t
LEFT JOIN LATERAL jsonb_array_elements(to_jsonb(t.Communication)) elem
  ON (elem ->> 'typ')::INTEGER = 1 
  AND (elem ->> 'kontext')::INTEGER = 2;

说明： #

• 用LEFT JOIN LATERAL可以保证没有符合条件的手机号时，原有的Name、FirstName记录不会被过滤，Mobilenumber会返回NULL
• 如果Communication字段本身就是JSON/JSONB类型，直接去掉to_jsonb()包装，直接传字段名即可
• 若你更习惯用JSON类型而非JSONB，把所有jsonb前缀的函数替换为json前缀即可（如json_array_elements、to_json）

多匹配项兼容场景 #

如果存在多条同时满足typ=1且kontext=2的记录，希望每个原行只返回一条结果，可通过聚合函数处理：

SELECT 
  t.Name,
  t.FirstName,
  string_agg(elem ->> 'adresse', ',') AS Mobilenumber
FROM your_table_name t
LEFT JOIN LATERAL jsonb_array_elements(to_jsonb(t.Communication)) elem
  ON (elem ->> 'typ')::INTEGER = 1 
  AND (elem ->> 'kontext')::INTEGER = 2
GROUP BY t.Name, t.FirstName;

说明： #

• 以上代码会把多个匹配的手机号用逗号拼接成字符串返回
• 如果只需取第一个匹配的手机号，把string_agg(elem ->> 'adresse', ',')替换为(array_agg(elem ->> 'adresse'))[1]即可

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