Sorting JSON values by field type in SQL

Question

I have two tables:

attribute
id          project_id  name          type
"556e28ab"  "14b106fd"  "running_id"  "INTEGER"
"7c78e6a4"  "14b106fd"  "headline"    "TEXT"

record
id          project_id  data
"63d7af84"  "14b106fd"  "{""headline"": ""Mike Tyson set to retire after loss"", ""running_id"": 0}"
"055e85ff"  "14b106fd"  "{""headline"": ""Iraqi vote remains in doubt"", ""running_id"": 1}"
"2dd0acdc"  "14b106fd"  "{""headline"": ""Conservatives Ponder Way Out of Wilderness"", ""running_id"": 2}"
"d8b82916"  "14b106fd"  "{""headline"": ""Final report blames instrument failure for Adam Air Flight 574 disaster"", ""running_id"": 3}"
"a71268cf"  "14b106fd"  "{""headline"": ""Kim Sets a Skating Record and Wins Her First World Title"", ""running_id"": 4}"

I want to sort values by running_id, inside the data json. If I do this, the sort will be considering running_id as an STRING and not as INTEGER, as it should be.

SELECT *
FROM record r
WHERE r.project_id = '14b106fd'
ORDER BY (r.data ->>'running_id') ASC;

I could cast it to INTEGER, but that is not a good solution because I want to be able to order by any field inside the json and these fields are not defined by me. I would like to find a way to cast it without explicitly defining the type or using the type from the attribute table, which I already tried but it is interpreted as STRING and not as a type.

I also already tried the following code:

SELECT r.*, a.data_type
FROM record r
JOIN attribute a ON (a.name = 'running_id' AND a.project_id = r.project_id)
WHERE r.project_id = '14b106fd'
ORDER BY
    CASE CAST(a.data_type AS TEXT)
        WHEN 'INTEGER' THEN CAST(r.data ->> 'running_id' AS INTEGER)
        WHEN 'NUMERIC' THEN CAST(r.data ->> 'running_id' AS NUMERIC)
        ELSE r.data ->>'running_id'
    END;

JHH · Accepted Answer · 2022-12-14 04:57:53Z

Assume you have another json field running_txt, which is of json string data type. You want to sort by either running_id or running_txt

sort by running_id as integer data type:

select id,
       project_id,
       data
  from ibiscp_record
 order by case when json_typeof(data->'running_id')='number' then cast(data->>'running_id' as integer) end , data->>'running_id';

Outcome:

id      |project_id|data                                                                                                                        |
--------+----------+----------------------------------------------------------------------------------------------------------------------------+
63d7af84|14b106fd  |{"headline": "Mike Tyson set to retire after loss", "running_id": 0, "running_txt": "0"}                                    |
2dd0acdc|14b106fd  |{"headline": "Conservatives Ponder Way Out of Wilderness", "running_id": 2, "running_txt": "2"}                             |
d8b82916|14b106fd  |{"headline": "Final report blames instrument failure for Adam Air Flight 574 disaster", "running_id": 3, "running_txt": "3"}|
a71268cf|14b106fd  |{"headline": "Kim Sets a Skating Record and Wins Her First World Title", "running_id": 4, "running_txt": "4"}               |
055e85ff|14b106fd  |{"headline": "Iraqi vote remains in doubt", "running_id": 11, "running_txt": "11"}                                          |

sort by running_txt as string data type (need to change json field name from running_id to running_txt, the rest query remains unchanged)

select id,
       project_id,
       data
  from ibiscp_record
 order by case when json_typeof(data->'running_txt')='number' then cast(data->>'running_txt' as integer) end , data->>'running_txt';

Result:

id      |project_id|data                                                                                                                        |
--------+----------+----------------------------------------------------------------------------------------------------------------------------+
63d7af84|14b106fd  |{"headline": "Mike Tyson set to retire after loss", "running_id": 0, "running_txt": "0"}                                    |
055e85ff|14b106fd  |{"headline": "Iraqi vote remains in doubt", "running_id": 11, "running_txt": "11"}                                          |
2dd0acdc|14b106fd  |{"headline": "Conservatives Ponder Way Out of Wilderness", "running_id": 2, "running_txt": "2"}                             |
d8b82916|14b106fd  |{"headline": "Final report blames instrument failure for Adam Air Flight 574 disaster", "running_id": 3, "running_txt": "3"}|
a71268cf|14b106fd  |{"headline": "Kim Sets a Skating Record and Wins Her First World Title", "running_id": 4, "running_txt": "4"}               |

Collectives™ on Stack Overflow

Sorting JSON values by field type in SQL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related