Postgres在json上查询空值

Question

我在JSON中有一个查询，用于根据JSON字段中的数据筛选出数据。

表名：audit_rules列名：rule_config(json(

rule_config包含JSON，其中包含"application_category"作为属性。

示例

{
"applicable_category":[
{
"status":"active",
"supported":"yes",
"expense_type":"Meal",
"acceptable_variation":0.18,
"minimum_value":25.0
},
{
"status":"active",
"supported":"yes",
"expense_type":"Car Rental",
"acceptable_variation":0.0,
"minimum_value":25.0
},
{
"status":"active",
"supported":"yes",
"expense_type":"Airfare",
"acceptable_variation":0.0,
"minimum_value":75
},
{
"status":"active",
"supported":"yes",
"expense_type":"Hotel",
"acceptable_variation":0.0,
"minimum_value":75
}
],
"minimum_required_keys":[
"amount",
"date",
"merchant",
"location"
],
"value":[
0,
0.5
]
}

但是有些行没有任何数据，或者其中没有"application_category"属性。

所以在运行以下查询时，我得到了错误：

select s.*,j from 
audit_rules  s 
cross join lateral json_array_elements ( s.rule_config#>'{applicable_category}' ) as j
WHERE j->>'expense_type' in ('Direct Bill');

错误：SQL错误[22023]：错误：无法在标量上调用json_array_elements

Answer 1

您可以将结果限制为仅包含数组的行：

select j.*
from audit_rules  s 
cross join lateral json_array_elements(s.rule_config#>'{applicable_category}') as j
WHERE json_typeof(s.rule_config -> 'applicable_category') = 'array'
and j ->> 'expense_type' in ('Meal')