Snippet: MySQL Sort Nulls Last Using JSON_EXTRACT

If you’re looking for a query, here you go. If you’re looking for an explanation, well, that might come in a few years.

SELECT *, JSON_EXTRACT(json, '$.list_order') AS list_order,  (JSON_TYPE(JSON_EXTRACT(json, '$.list_order')) = 'NULL') AS null_order ORDER BY null_order ASC, list_order ASC

Long story short, MySQL differs between an internal NULL and a JSON null (see links below). However, JSON_TYPE parses this out for us and allows us to use the comparative = ‘NULL’.

This is essentially the same as the default way you would do a sort with normal MySQL columns (see first link):

SELECT *, list_order, ISNULL(list_order) FROM products
ORDER BY ISNULL(list_order) ASC, list_order ASC;

Or like the following shortcut seen in the last SO link:

SELECT *, list_order, ISNULL(list_order) FROM products
ORDER BY -list_order DESC;

Better explanation to come. Peace.

Like what you read? Give Sheng Slogar a round of applause.

From a quick cheer to a standing ovation, clap to show how much you enjoyed this story.