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

Question

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

*This is the expectation* Trim leading `'dnn'`, convert to numeric then sort. *But currently, It gets sorted in binary order, mysql natural sorting* This is common string values sorting. Alphabetical sorting. — Akina, Dec 02 '22 at 05:06
Please note: These strings can be anything, it could also be 1dnn,dnn1, abc123gef, abc, 123 — Misbha Afreen, Dec 02 '22 at 08:25

score 0 · Accepted Answer · edited Dec 06 '22 at 06:24

0

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.

edited Dec 06 '22 at 06:24

Felix G

724
5
17

answered Dec 02 '22 at 09:36

Misbha Afreen

11
5

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

1 Answers1