How to convert strings extracted from JSON to a numeric type for aggregates? I am struggling to extract data from this JSON object in PostgreSQL:
&quot;temperature&quot;: {
&quot;boiler_temp&quot;: {
  &quot;values&quot;: [
    &quot;1&quot;,
    &quot;2&quot;,
    &quot;3&quot;,
    &quot;3&quot;,
    &quot;5&quot;,
    &quot;5&quot;,
    &quot;5&quot;,
    &quot;5&quot;,
    &quot;5&quot;,
    &quot;5&quot;,
    &quot;5&quot;,
    &quot;5&quot;,
    &quot;5&quot;,
    &quot;5&quot;,
    &quot;5&quot;,
    &quot;5&quot;,
    &quot;5&quot;,
    &quot;6&quot;,
    &quot;6&quot;,
    &quot;6&quot;,
    &quot;4&quot;,
    &quot;7&quot;,
    &quot;7&quot;,
    &quot;7&quot;,
    &quot;7&quot;,
    &quot;4&quot;,
    null,
    &quot;4&quot;,
    &quot;5&quot;,
    &quot;5&quot;,
    &quot;5&quot;,
    &quot;6&quot;,
    &quot;6&quot;,
    &quot;6&quot;,
    &quot;6&quot;,
    &quot;6&quot;,
    &quot;6&quot;,
    &quot;6&quot;,
    &quot;6&quot;,
    &quot;6&quot;,
    &quot;5&quot;,
    &quot;5&quot;,
    &quot;5&quot;,
    &quot;5&quot;,
    &quot;5&quot;,
    &quot;5&quot;,
    &quot;5&quot;,
    &quot;5&quot;,
    &quot;2&quot;,
    &quot;2&quot;
  ]
}
}

I am using jsonb_array_elements() to extract it but they are returned as a string like '1', '2' and so on and so forth. My goal is to try and find the &quot;avg&quot;: 5,&quot;max&quot;: 7,&quot;min&quot;: 1 for this data set however because I am returning them as string I am getting a value of null and an error:
My current code:
(select MIN(x::text::int) from jsonb_array_elements((data#&gt;&gt;'{temperature,boiler_temp}')::jsonb-&gt;'values') AS x) as mininum_temp

And I am getting an error:

ERROR: Invalid Syntax for integer :&quot;&quot;1&quot;&quot;
You need to extract them as text using jsonb_array_elements_text(), then you can cast it to an integer:
select min(x.val::int), max(x.val::int), avg(x.val::int)
from the_table
  cross join jsonb_array_elements_text(data #&gt; '{temperature,boiler_temp,values}') as x(val)