Numeric sort in JSON

April 2, 2023

I would like to sort a JSON field by the numeric value. Here is an example:

with tbl as (
  select json '1' j union all
  select json '2' j union all
  select json '"hello"' union all
  select json '"alpha"' union all
  select json '[1,2,3]'
) select j from tbl order by Float64(j)

However, this gives me an error saying (obviously):

The provided JSON input is not a number; error in FLOAT64 expression

MEDevel.com: Open-source for Healthcare and Education

Collecting and validating open-source software for healthcare, education, enterprise, development, medical imaging, medical records, and digital pathology.
Visit Medevel

I know this of course, but the result I’d like to see is something more like the TRY_CAST, that is: treating all non-numeric values as null. What would be the proper way to do this? One option as I was writing this is to do a round-about TO_JSON_STRING cast and then apply the TRY_CAST on that. For example:

with tbl as (
  select json '1' j union all
  select json '2' j union all
  select json '"hello"' union all
  select json '"alpha"' union all
  select json '[1,2,3]'
) select SAFE_CAST(to_json_string(j) as float64) from tbl

Is that the only way to do this?

>Solution :

You can try this as well.

with tbl as (
  select json '1' j union all
  select json '2' j union all
  select json '"hello"' union all
  select json '"alpha"' union all
  select json '[1,2,3]'
)
select SAFE.FLOAT64(j) from tbl;