Creating JSON objects from JSON object properties in MySQL query

Question

I need to create a list of JSON objects from properties in JSON object using MySQL 5.7. I have this structure:

{
    "X": 1,
    "Y": 1,
    "Z": 55,
    "A": 2,
    "B": 33
}

I want to have the properties to be separated as objects and sorted by the keys in those objects like this:

[
    {"A": 2},
    {"B": 3},
    {"X": 1},
    {"Y": 1},
    {"Z": 55}
]

I tried to separate keys and values and maybe then somehow merge it

TRIM(LEADING '[' FROM TRIM(TRAILING ']' FROM JSON_KEYS(letters_and_values)))           as letters,
TRIM(LEADING '[' FROM TRIM(TRAILING ']' FROM JSON_EXTRACT(letters_and_values, '$.*'))) as values,

But I feel I'm complicating it. Does anyone know the easiest way to achieve the expected result?

Answer 1

No decent solution exists in MySQL 5.x for splitting JSON to rows for sorting. One ugly solution is to cross join with a table containing values 0, 1, 2, ... and use JSON_EXTRACT(..., '$[...]') to extract each item from JSON. Once you have each item on its row, you can sort and re-combine:

SELECT
    CONCAT('[', GROUP_CONCAT(CONCAT('{', k, ':', v, '}') ORDER BY k DESC SEPARATOR ','), ']')
FROM (
    SELECT
        JSON_EXTRACT(JSON_KEYS(json), CONCAT('$[', i, ']')) AS k,
        JSON_EXTRACT(json, CONCAT('$.', JSON_EXTRACT(JSON_KEYS(json), CONCAT('$[', i, ']')))) AS v
    FROM t
    INNER JOIN (
        SELECT 0 AS i UNION ALL
        SELECT 1      UNION ALL
        SELECT 2      UNION ALL
        SELECT 3      UNION ALL
        SELECT 4      UNION ALL
        SELECT 5      UNION ALL
        SELECT 6      UNION ALL
        SELECT 7      UNION ALL
        SELECT 8      UNION ALL
        SELECT 9
    ) AS numbers ON i < JSON_LENGTH(json)
) AS x

Demo

In MySQL 8 you can use JSON_TABLE function to manipulate the JSON.

Note that the keys in JSON objects do not have any natural order. {"x": 1, "y": 2} and {"y": 2, "x": 1} are identical.