json - PostgreSQL - How to get the count of elements in a column list -

this how orders table looks :

----------------------------------------------------------- | 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 }] ----------------------------------------------------------- 

the results wanted when doing count json elements inside brackets where active == true in each element :

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

this i'm using doesn't give data i'm looking because doesn't not each dictionary see if active == true

select id, json_array_length(order::json) orders  ------------ id  | counts ------------ |1  |   2 |2  |   1 |3  |   1 |4  |   3 ------------ 

use json_array_elements() selects elements of json array, filter elements , count remaining elements grouping id.

select id, count(id) orders, json_array_elements(orders) elem (elem->>'active')::boolean group 1 order 1;   id | count  ----+-------   1 |     1   2 |     1   3 |     1   4 |     3 (4 rows)     

notes:

• use set returning functions (like json_array_elements()) in from clause lateral join;
• json boolean values should true (not true);
• there no money type in json, use 300 instead of $300;
• use jsonlint verify json values.