Postgres 15

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

select version();

version

PostgreSQL 15.0 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-10), 64-bit

SELECT 1

create table has_json(id   integer  generated always as identity
                                     primary key 
                     ,val   json
                     ); 
                     
insert into has_json(val) 
     values ('{ "foo": "101", "bar": "George"}') 
          , ('{ "bar": "familiar","foo": "7"}')
          , ('{ "foo": "8", "something else": "Anderson"}');
          
select * 
  from has_json;

CREATE TABLE

INSERT 0 3

id
val

1
{ "foo": "101", "bar": "George"}

2
{ "bar": "familiar","foo": "7"}

3
{ "foo": "8", "something else": "Anderson"}

SELECT 3

select * 
  from has_json 
 order by cast (val ->> 'foo' AS DECIMAL);

id
val

2
{ "bar": "familiar","foo": "7"}

3
{ "foo": "8", "something else": "Anderson"}

1
{ "foo": "101", "bar": "George"}

SELECT 3

select * 
  from has_json  
 where val ->> 'bar' is not null 
 order by cast (val ->> 'foo' as decimal);

id
val

2
{ "bar": "familiar","foo": "7"}

1
{ "foo": "101", "bar": "George"}

SELECT 2

create or replace function cast_number(str varchar)
    returns decimal
    immutable
    language sql
as $$
 select cast(str as decimal);
$$;

CREATE FUNCTION

select * 
  from has_json 
 order by cast_number(val ->> 'foo');

id
val

2
{ "bar": "familiar","foo": "7"}

3
{ "foo": "8", "something else": "Anderson"}

1
{ "foo": "101", "bar": "George"}

SELECT 3

id	val
1	{ "foo": "101", "bar": "George"}
2	{ "bar": "familiar","foo": "7"}
3	{ "foo": "8", "something else": "Anderson"}