CASE on ORDER BY based on field type

Question

How can one execute ORDER BY taking into consideration the field value type?

CREATE TABLE public.test(
    data jsonb
);

TRUNCATE test;

INSERT INTO test (data) VALUES ('{"age":2, "name": "b"}');
INSERT INTO test (data) VALUES ('{"age":1, "name": "cc"}');
INSERT INTO test (data) VALUES ('{"age":4, "name": "d"}');
INSERT INTO test (data) VALUES ('{"age":33, "name": "a"}');

-- works
SELECT * FROM test ORDER BY data->>'name' ASC; 
-- works
SELECT * FROM test ORDER BY (data->>'age')::numeric ASC;

-- does not work
/*
ERROR:  CASE types text and numeric cannot be matched
LINE 5:         WHEN 'number' THEN (data->>'age')::numeric
*/
SELECT data->>'name' as name, data->>'age' as age 
FROM test
ORDER BY
    CASE jsonb_typeof(data->'age')
        WHEN 'number' THEN (data->>'age')::numeric
        ELSE data->>'age'
    END
ASC;

(The actual field name will be injected into the query from code)

user_2696355 · Answer

You could simply order by data->'age' or data->'name' without casting it in some cases.

(note the use of the -> operator which returns a JSONB value, instead of the ->> which always returns text)

This seems to work in both cases:

SELECT data->>'name' as name, data->>'age' as age 
FROM test
ORDER BY data->'age' ASC;
-- name     age
-- cc         1
-- b          2
-- d          4
-- a         33

SELECT data->>'name' as name, data->>'age' as age 
FROM test
ORDER BY data->'name' ASC;
-- name     age
-- a         33
-- b          2
-- cc         1
-- d          4

CASE on ORDER BY based on field type

1 Answers