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

Question

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!

GMB · Accepted Answer · 2022-11-11 22:27:08Z

2

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

answered Nov 11, 2022 at 22:27

GMB

224k25 gold badges103 silver badges151 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Chris Over a year ago

Exactly what I needed to learn - thank you very much for your help !

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related