PostgreSQL jsonb：使用jsonpath获取实际数据类型值的方法

问题描述 #

在PostgreSQL中使用jsonb列时，希望通过jsonpath选择/转换深层路径的属性为实际数据类型（而非带引号的字符串），避免使用CAST和->/->>这类构造（因为要选择35+个深层属性，后者会让查询过于复杂）。尝试的查询及报错如下：

Select 
    PolicyNumber AS "POLICYNUMBER",
    jsonb_path_query(payload, '$.**.ProdModelID')::text   AS "PRODMODELID",
    jsonb_path_query(payload, '$.**.CashOnHand')::float   AS "CASHONHAND"
from policy_json_table

错误信息：

SQL Error [22023]: ERROR: cannot cast jsonb string to type double precision

解决方案 #

完全可以通过jsonpath实现需求，不需要依赖->/->>逐个节点处理，以下是具体解决方法：

1. 提取文本类型并去除引号
   使用jsonpath内置的text()函数，直接将json字符串值转换为PostgreSQL文本类型，避免转成jsonb再转text时保留引号：

   jsonb_path_query(payload, '$.**.ProdModelID.text()') AS "PRODMODELID"
   

2. 正确转换数值类型
   报错原因是CashOnHand在jsonb中是字符串格式（比如"123.45"），而非原生数值类型。需要先用jsonpath的number()函数将其转换为json数值，再转为PostgreSQL的float类型：

   jsonb_path_query(payload, '$.**.CashOnHand.number()')::float AS "CASHONHAND"
   

   如果CashOnHand本身就是json原生数值（比如123.45），直接转float即可，无需number()：

   jsonb_path_query(payload, '$.**.CashOnHand')::float AS "CASHONHAND"
   

3. 批量处理多个属性
   对于35+个深层属性，只需重复上述模式即可，每个属性用对应的jsonpath类型函数处理，无需拆解路径节点，保持查询简洁：

   Select 
       PolicyNumber AS "POLICYNUMBER",
       jsonb_path_query(payload, '$.**.ProdModelID.text()') AS "PRODMODELID",
       jsonb_path_query(payload, '$.**.CashOnHand.number()')::float AS "CASHONHAND",
       jsonb_path_query(payload, '$.**.CreateTime.text()') AS "CREATETIME",
       jsonb_path_query(payload, '$.**.TotalAmount.number()')::numeric AS "TOTALAMOUNT"
       -- 继续添加其他属性...
   from policy_json_table
   

注意事项 #

• 如果同一个路径匹配到多个值，jsonb_path_query会返回多行结果。如果确定每个属性只有一个匹配值，建议使用jsonb_path_query_first替代，避免结果集膨胀。
• 确保jsonpath表达式的准确性，$.**会递归遍历所有层级，若存在同名属性可能返回非预期值，必要时可以缩小路径范围（比如$.PolicyDetails.**）。

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