How to Create a JSON_OBJECT in MySql From Rows of Key-Value Data-CodePudding

In MySQL, I am trying to create a JSON_OBJECT from rows of data containing key-value pairs.

Here is the sample data:

CREATE TABLE TempValuePair( MyKey VARCHAR(64), MyValue VARCHAR(64) );
INSERT INTO TempValuePair VALUE 
    ('Country', 'Argentina'), 
    ('Capital', 'Buenos Aires'), 
    ('Population', 45810000 );

The following statement seems to return an argument that conforms to the JSON_OBJECT requirements:

SELECT GROUP_CONCAT( 
    CONCAT( '\'', MyKey, '\',\'', Myvalue, '\'' ) 
    ORDER BY MyKey 
) 
FROM TempValuePair;

However, the following statement fails:

    SELECT GROUP_CONCAT( 
        CONCAT( '\'', MyKey, '\',\'', Myvalue, '\'' ) 
        ORDER BY MyKey 
    ) 
    FROM TempValuePair 
);

Any advice about what I am doing wrong would be greatly appreciated. Thanks!

CodePudding user response：

You seem to want json_objectagg, which is available in MySQL since version 5.7. The function aggregates key/value pairs from multiple rows into a single JSON object:

select json_objectagg(mykey, myvalue) as js from TempValuePair;

Yields:

{"Capital": "Buenos Aires", "Country": "Argentina", "Population": "45810000"}

Demo on DB Fiddle