/* Available in MySQL and SQL Azure */
SELECT 
    Name,
    CountryCode,
    JSON_ARRAYAGG(Name) over p_code AS json_array,
    JSON_OBJECTAGG(Name, Population) over p_code AS json_object
FROM city
WINDOW p_code AS (PARTITION BY CountryCode)

                                                                                                                                                             

Name	CountryCode	json_array	json_object
Oranjestad	ABW	["Oranjestad"]	{"Oranjestad": 29,034}
Kabul	AFG	["Kabul", "Qandahar",   "Herat", "Mazar-e-Sharif"]	{"Herat": 186,800,   "Kabul": 1,780,000, "Qandahar": 237,500,   "Mazar-e-Sharif": 127,800}
Qandahar	AFG	["Kabul", "Qandahar",   "Herat", "Mazar-e-Sharif"]	{"Herat": 186,800,   "Kabul": 1,780,000, "Qandahar": 237,500,   "Mazar-e-Sharif": 127,800}
Herat	AFG	["Kabul", "Qandahar",   "Herat", "Mazar-e-Sharif"]	{"Herat": 186,800,   "Kabul": 1,780,000, "Qandahar": 237,500,   "Mazar-e-Sharif": 127,800}
Mazar-e-Sharif	AFG	["Kabul", "Qandahar",   "Herat", "Mazar-e-Sharif"]	{"Herat": 186,800,   "Kabul": 1,780,000, "Qandahar": 237,500,   "Mazar-e-Sharif": 127,800}
Namibe	AGO	["Namibe", "Benguela",   "Lobito", "Huambo", "Luanda"]	{"Huambo": 163,100,   "Lobito": 130,000, "Luanda": 2,022,000, "Namibe":   118,200, "Benguela": 128,300}
Benguela	AGO	["Namibe", "Benguela",   "Lobito", "Huambo", "Luanda"]	{"Huambo": 163,100,   "Lobito": 130,000, "Luanda": 2,022,000, "Namibe":   118,200, "Benguela": 128,300}
Lobito	AGO	["Namibe", "Benguela",   "Lobito", "Huambo", "Luanda"]	{"Huambo": 163,100,   "Lobito": 130,000, "Luanda": 2,022,000, "Namibe":   118,200, "Benguela": 128,300}
Huambo	AGO	["Namibe", "Benguela",   "Lobito", "Huambo", "Luanda"]	{"Huambo": 163,100,   "Lobito": 130,000, "Luanda": 2,022,000, "Namibe":   118,200, "Benguela": 128,300}
...

JSON_ARRAYAGG - Return result set as a single JSON array. JSON_OBJECTAGG - Return result set as a single JSON object.