PostgreSQL中如何将JSONB字段存入变量？当前获取值为空

你执行以下SQL尝试将JSONB字段值存入变量in_batch_id时，结果为空：

SELECT ((payload->>'99')::jsonb)->>'BATCH_ID' into in_batch_id 
  from history where id = table.ID;

对应的JSONB数据结构如下：

{
  "1": {
    "seq": null,
    "doc_id": null,
    "batch_id": "91"
  },
  "2": {
    "seq": null,
    "doc_id": null,
    "batch_id": "91"
    
  },
  "99": {
    "seq": null,
    "doc_id": null,
    "emp_id": null,
    "batch_id": 91
   }
}

问题原因 #

PostgreSQL的JSON/JSONB键名区分大小写，你SQL里用的是大写的'BATCH_ID'，但JSON结构中对应的键是小写的'batch_id'，导致匹配不到值，所以变量结果为空。

修正后的SQL #

直接把键名改成小写即可，同时可以简化SQL写法（不需要额外转成jsonb，->>已经返回text类型，嵌套取值可以用#>>路径语法更简洁）：

方法1：简化嵌套写法

SELECT payload #>> '{99, batch_id}' INTO in_batch_id
FROM history WHERE id = table.ID;

方法2：保持原嵌套逻辑，修正键名

SELECT ((payload->>'99')::jsonb)->>'batch_id' INTO in_batch_id
FROM history WHERE id = table.ID;

额外说明 #

如果in_batch_id是数值类型变量，你可以用->获取jsonb类型后再转成对应数值类型，比如：

SELECT (payload #> '{99, batch_id}')::int INTO in_batch_id
FROM history WHERE id = table.ID;

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