0

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""
2
  • Aside: Your JSON should store number or integer primitives not strings. jsonb_array_elements_text() returns text either way, but then we could be sure to get valid numeric literals that can be cast to numeric (or integer) safely - else an exception is raised. About jsonb_array_elements_text(): dba.stackexchange.com/a/54289/3684 Apr 22, 2021 at 16:38
  • exactly my thoughts, but the system was built like that and I am creating a report for it. Apr 26, 2021 at 3:42

1 Answer 1

1

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 #> '{temperature,boiler_temp,values}') as x(val)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.