I am struggling to extract data from this JSON object in PostgreSQL:
"temperature": {
"boiler_temp": {
"values": [
"1",
"2",
"3",
"3",
"5",
"5",
"5",
"5",
"5",
"5",
"5",
"5",
"5",
"5",
"5",
"5",
"5",
"6",
"6",
"6",
"4",
"7",
"7",
"7",
"7",
"4",
null,
"4",
"5",
"5",
"5",
"6",
"6",
"6",
"6",
"6",
"6",
"6",
"6",
"6",
"5",
"5",
"5",
"5",
"5",
"5",
"5",
"5",
"2",
"2"
]
}
}
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 "avg": 5,"max": 7,"min": 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#>>'{temperature,boiler_temp}')::jsonb->'values') AS x) as mininum_temp
And I am getting an error:
ERROR: Invalid Syntax for integer :""1""
Best Answer
You need to extract them as text using
jsonb_array_elements_text()
, then you can cast it to an integer: