PostgreSQL - How to get the count of elements in a column list
Use json_array_elements() which selects all elements of the json array, filter the elements and finally count remaining elements grouping by id.
select id, count(id)from orderscross join json_array_elements(orders) elemwhere (elem->>'active')::booleangroup by 1order by 1;Live demo in Db<>fiddle.
Notes:
- use set returning functions (like
json_array_elements()) inFROMclause as lateral join; - json boolean values should look like
true(notTRUE); - there is no
moneytype in json, use300instead of$300; - use jsonlint to verify json values.
I started by normalizing the orders by each order using json_array_elements and then was able to do the count and check if the active = TRUE
WITH normalize_all_orders AS ( SELECT id , json_array_elements(order::JSON) as order_line FROM orders)SELECT id , COUNT(order_line) AS orders_countsWHERE order_line::json->>'soundFlag' = 'true'GROUP BY id