JSON_ARRAYAGG & JSON_OBJECTAGG & GROUP_CONCAT in mysql

create table cloth(

id int ,

attribute varchar(50),

value varchar(50)

);

INSERT INTO cloth(id,attribute,value)

values(2,'color','red'),(2,'fabric','silk');

INSERT INTO cloth(id,attribute,value)

values(3,'color','green'),(3,'shape','square');

INSERT INTO cloth(id,attribute,value) values(2,'color','blue');

SELECT id,attribute,value from cloth;

Output:

+------+-----------+--------+

| id | attribute | value |

+------+-----------+--------+

| 2 | color | red |

| 2 | fabric | silk |

| 3 | color | green |

| 3 | shape | square |

| 2 | color | blue |

+------+-----------+--------+

SELECT id, JSON_OBJECTAGG(attribute, value) FROM cloth GROUP BY id;

Output:

+------+---------------------------------------+

| id | JSON_OBJECTAGG(attribute, value) |

+------+---------------------------------------+

| 2 | {"color": "blue", "fabric": "silk"} |

| 3 | {"color": "green", "shape": "square"} |

+------+---------------------------------------+

2 rows in set (0.00 sec)

Takes two column names or expressions as arguments, the first of these being

used as a key and the second as a value, and returns a JSON object containing

key-value pairs.

Here id 2 has two color red & blue ,last one is blue so in output blue value

come.

Array Aggregation:

Aggregates a result set as a single JSON array whose elements consist of the

rows.

SELECT id, JSON_ARRAYAGG(attribute) FROM cloth GROUP BY id;

Output:

+------+------------------------------+

| id | JSON_ARRAYAGG(attribute) |

+------+------------------------------+

| 2 | ["color", "fabric", "color"] |

| 3 | ["color", "shape"] |

+------+------------------------------+

GROUP_CONCAT

SELECT id, GROUP_CONCAT(attribute) FROM cloth GROUP BY id;

This function returns a string result with the concatenated non-NULL values

from a group.

Output:

+------+-------------------------+

| id | GROUP_CONCAT(attribute) |

+------+-------------------------+

| 2 | color,fabric,color |

| 3 | color,shape |

+------+-------------------------+

Function in somesense similar to JSON_ARRAYAGG instead of array here we

get values seperated by commas