Postgres中JSON列嵌套对象值查询：按指定code提取对应bezeichnung_de的方法

基础查询（固定传入code值） #

假设你的表名为my_table，存储JSON的列名为json_data，以查询code=86为例，语句如下：

SELECT
  '86' AS "Code",
  json_data -> 'objekt_art' -> '86' ->> 'bezeichnung_de' AS "Bez"
FROM my_table
-- 过滤不存在对应code的行，JSONB类型用以下条件
WHERE json_data -> 'objekt_art' ? '86'
-- JSON类型请替换成下面的条件
-- WHERE json_data -> 'objekt_art' -> '86' IS NOT NULL

关联其他表code查询 #

如果你需要匹配的code存储在另一张关联表（比如表名为other_table，code字段名为code），可以先拆析JSON再关联查询：

SELECT
  art.value ->> 'code' AS "Code",
  art.value ->> 'bezeichnung_de' AS "Bez"
FROM my_table t
-- JSON类型用json_each，JSONB类型替换为jsonb_each性能更好
, json_each(t.json_data -> 'objekt_art') art
INNER JOIN other_table ot 
  ON ot.code = art.value ->> 'code'
-- 可选：添加过滤条件指定具体code
-- WHERE ot.code = '86'

运算符说明 #

• ->：返回JSON类型的子节点，可继续嵌套访问下级节点
• ->>：返回文本类型的JSON节点值，适合直接读取字段内容
• ?：仅JSONB类型支持，用于判断JSON对象是否包含指定key

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