(PostgreSQL) Query something in a JSON array in a WHERE clause

pgsql-query-json-array.sql

-- when you have a record which looks like this 
--
--   id: 5,
--   properties: {
--     ages: [20, 30]
--   }
--
-- it is a bit of a pain if you need to query based on the contents of the "ages" array inside the JSON object "properties"
-- because PG currently lacks easy to use operators to work with JSON arrays 

-- select groups where all ages are under 40

SELECT * FROM groups
WHERE 40 > (
  SELECT max(n) FROM cast(cast(json_array_elements(properties->'size') as text) as integer) n
);

-- select groups where the sum of all ages is 50

SELECT * FROM groups
WHERE 50 > (
  SELECT sum(n) FROM cast(cast(json_array_elements(properties->'size') as text) as integer) n
);