Creating dummy from JSON values and using it in the WHERE clause The variable I'm interested in is an answer to a questionary, stored in a JSON column in a table called Questions. What I need is the number of users who've answered 'no' to a question coded as hasDefaulted. The way I see it, I need to first parse the JSON column, create a dummy and then use it in the WHERE clause, but the exact syntax is just beyond me. Since the data is proprietary, I'll hide the FROM clause - please trust me it's correctly written.

SELECT 
COUNT(*) as count,
CREATE INDEX dummy ON dummy(cast("answer"-&gt;&gt;'no' AS int))

FROM CLAUSE HERE

WHERE 
_____CONDITION1, omitted____
AND "Questions"."code" = 'hasDefaulted'
AND dummy = 1
I've conceived an unelegant but working approach using a subquery:

SELECT COUNT(*) as has_defaulted

FROM (
SELECT answer::json-&gt;&gt;'value' AS resp
_FROM CLAUSE HERE_
WHERE CONDITION 1
  AND "code" = 'hasDefaulted' ) as subquery
  GROUP BY subquery.resp
  ORDER BY subquery.resp ASC
  LIMIT 1)