is there a way to extract duplicated row value in sql as the key/grouping value?

Question

I have following two tables

users

id | name

1  | john
2  | ada

events

id |   content   | userId

1  | 'applied'   | 1
2  | 'interviewed| 1

What would be the query that returns data in the following shape:

[
  {name:'john', events:[{id:1, content:'applied'},{id:2, content:'interviewed'}]}
]

I have tried to run following queries

attempt 1

select events.id, content, users.name 
from events 
left join users 
on users.id=events.userId 
where events.userId = ?

but it return duplicated value for the name as following

[
{
"id": 1,
"content": "ronaldo",
"name": "Norman Zboncak"
},
{
"id": 2,
"content": "messi",
"name": "Norman Zboncak"
},
{
"id": 3,
"content": "messi",
"name": "Norman Zboncak"
}
]

attempt 2

I tried to use group_concat but apparently you cannot pas multiple arguments into it so couldn't get the result in the desired shape

Answer 1

You must do a LEFT join of users to events and aggregate with SQLite's JSON Functions:

SELECT json_object(
          'name', u.name, 
          'events', json_group_array(json_object('id', e.id, 'content', e.content))
       ) result
FROM users u LEFT JOIN events e
ON e.userId = u.id  
WHERE u.id = 1 -- remove this line to get results for all users
GROUP BY u.id;

See the demo.