arrays - PostgreSQL - 如何获取列列表中的元素数

Question

这是我的订单表的样子:

-----------------------------------------------------------
| id  | order
-----------------------------------------------------------
|1    |[{"order_quantity" : 2, "active" : TRUE, "price" : $100 }, {"order_quantity" : 4, "active" : FALSE, "price" : $200 }]
|2    |[{"order_quantity" : 2, "active" : TRUE, "price" : $170 }]
|3    |[{"order_quantity" : 2, "active" : TRUE, "price" : $120 }]
|4    |[{"order_quantity" : 2, "active" : TRUE, "price" : $150 }, {"order_quantity" : 3, "active" : TRUE, "price" : $200 }, {"order_quantity" : 5, "active" : TRUE, "price" : $200 }]
-----------------------------------------------------------

对每个元素中括号 WHERE active == TRUE 中的 JSON 元素进行计数时所需的结果:

------------
id  | counts
------------
|1  |   1
|2  |   1
|3  |   1
|4  |   3
------------

这就是我正在使用的，但它没有提供我正在寻找的数据，因为它没有查看每个字典以查看是否 active == TRUE

SELECT id, json_array_length(order::JSON)
FROM orders

------------
id  | counts
------------
|1  |   2
|2  |   1
|3  |   1
|4  |   3
------------

Answer 1

使用json_array_elements()选择json数组的所有元素，过滤元素，最后统计剩余元素按id分组。

select id, count(id)
from orders
cross join json_array_elements(orders) elem
where (elem->>'active')::boolean
group by 1
order by 1;

现场演示 Db<>fiddle.

注意事项:

• 在 FROM 子句中使用集合返回函数(如 json_array_elements())作为 lateral join ;
• json bool 值应该看起来像 true(不是 TRUE)；
• json中没有money类型，使用300代替$300；
• 使用jsonlint验证 json 值。