By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table test (country varchar(50), city varchar(50));
insert into test values ('US', 'New York');
insert into test values ('US', 'Las Vegas');
insert into test values ('France', 'Paris');
insert into test values ('Canada', 'Toranto');
insert into test values ('Canada', 'Vancouver');
5 rows affected
select DISTINCT country as country,json_query((select CONCAT('[','"', STRING_AGG(city, '","'), '"',']')
from test b
where b.country=a.country
)) as city
from test a
for json path
| JSON_F52E2B61-18A1-11d1-B105-00805F49916B |
|---|
| [{"country":"Canada","city":["Toranto","Vancouver"]},{"country":"France","city":["Paris"]},{"country":"US","city":["New York","Las Vegas"]}] |