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

Question

This is how my orders table looks like :

-----------------------------------------------------------
| 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 the count for the JSON elements inside the brackets WHERE active == TRUE in each element :

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

This is what I'm using but it doesn't give the data i'm looking for because it doesn't not look into each dictionary to see if active == TRUE

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

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

Answer 1

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 orders, json_array_elements(orders) elem
where (elem->>'active')::boolean
group by 1
order by 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 as lateral join;
• json boolean values should look like true (not TRUE);
• there is no the money type in json, use 300 instead of $300;
• use jsonlint to verify json values.