mysqlsqlsortingalphanumericjson-value

How to sort JSON_EXTRACT or JSON_VALUE value in alphanumeric order in sql

SELECT * 
FROM config_server_db.configuration_item 
where configuration_item.topic_info_id = 96 AND FIND_IN_SET('6f517a22-2df5-4b75-30af-bce2bd7b066a', labels)  
order by JSON_VALUE(configuration_item.cfg_value, '$."rows"."4af8ecaf-4437-615a-7abd-937cd6883ce6"') desc;

If descending the row value must be sorted in alphanumeric order something like this and if it is ascending then reverse way. This is the expectation.

dnn16
dnn13
dnn11
dnn10
dnn9
dnn8
dnn7
dnn6
dnn3 
dnn1

But currently, It gets sorted in binary order, mysql natural sorting

dnn9
dnn8
dnn7
dnn6
dnn3
dnn16
dnn13
dnn11
dnn1

cfg_value is something like this

{
    "tableId": "6f517a22-2df5-4b75-30af-bce2bd7b066a",
    "rows": {
        "4af8ecaf-4437-615a-7abd-937cd6883ce6": "dnn9"
    }
}

Please note: These strings can be anywhere and anything, it could also be 1dnn,dnn1, abc123gef, abc, 123

Solution

  • This worked https://dev.mysql.com/doc/refman/8.0/en/string-functions.html#function_lpad

    ORDER BY lpad(JSON_VALUE(configuration_item.cfg_value, '$."rows"."4af8ecaf-4437-615a-7abd-937cd6883ce6"'), 10, 0)

    Assuming maximum string length is 10, you can adjust to a bigger length if you want to.