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?

There is no proper solution in MySQL 5.7. Is MySQL 8 an option? If not, do you have exactly/at most 5 keys in the object? — Salman A, Sep 13 '21 at 12:03
I am afraid MySQL 8 is not an option, and the number of keys is dynamic. — Borys Zielonka, Sep 13 '21 at 12:56
Why would you want this? And why are you doing string processing in a database? — MatBailie, Sep 13 '21 at 13:36

Salman A · Accepted Answer · 2021-09-13T13:31:32.323

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.

Thank you, the 'ugly' solution you suggested solves a problem, I added grouping https://dbfiddle.uk/?rdbms=mysql_5.7&fiddle=42c90cd3f191c655436525eb4b070113 and I must admit it works, but the query is too heavy for me. — Borys Zielonka, Sep 13 '21 at 16:08

Creating JSON objects from JSON object properties in MySQL query

1 Answers1