在Postgres中计数JSONB阵列

Question

我有一个Postgres数据库，其中一些列数据存储如下：

Guest_composition	Charging_age
[{“ A”：1，“ B”：1，“ C”：1，“ Children_ages”：[10，5，2，0.1]} ]	3
[{“ a”：1，“ b”：1，“ c”：1，“ children_ages”：[2.5，1，4]}]	3

我想浏览Children_ages阵列并返回计数超过3岁的孩子。我很难使用阵列数据，因为它是JSONB而不是int数组的返回。

第一行应该返回2，因为有2个孩子以上3岁。第二行应该返回1个，因为有1个孩子以上3岁。

我尝试了以下内容，但它没有起作用：

with with with with reservation AS (SELECT jsonb_array_elements(reservations.guest_composition)->'children_ages' as children_ages, charging_age FROM reservations

SELECT (CASE WHEN (reservations.charging_age IS NOT NULL AND reservation.children_ages IS NOT NULL) THEN SUM （何时（repervation.children_ages）:: int []＆gt; =（revervations.charging_age）:: int then 1 else 1 else 1 else 0 ense 0 end end end）as children_to_charge

Answer 1

您可以使用SQL JSON路径函数提取所有儿童年龄的数组：

select jsonb_path_query_array(r.guest_composition, '$[*].children_ages[*] ? (@ > 3)')
from reservations r;

该数组的长度是您要查找的计数：

select jsonb_array_length(jsonb_path_query_array(r.guest_composition, '$[*].children_ages[*] ? (@ > 3)'))
from reservations r;

如果charging_age_age是一列，我还不清楚该 数组每一行。如果是这样，您可以将参数传递给JSON路径函数：

select jsonb_path_query_array(
          r.guest_composition, '$[*].children_ages[*] ? (@ > $age)', 
          jsonb_build_object('age', charging_age)
       )
from reservations r;