PostgreSQL - How to get the count of elements in a column list
<p>This is how my orders table looks like :</p>

<pre><code>-----------------------------------------------------------
| 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 }]
-----------------------------------------------------------
</code></pre>

<p>the results wanted when doing the count for the <code>JSON</code> elements inside the brackets <code>WHERE active == TRUE</code> in each element :</p>

<pre><code>------------
id  | counts
------------
|1  |   1
|2  |   1
|3  |   1
|4  |   3
------------
</code></pre>

<p>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  <code>active == TRUE</code></p>

<pre><code>SELECT id, json_array_length(order::JSON)
FROM orders

------------
id  | counts
------------
|1  |   2
|2  |   1
|3  |   1
|4  |   3
------------
</code></pre>