-2

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

esentai
  • 69
  • 1
  • 10
  • I don't think this can be done in one query, unless you process data afterwards. Your desired result is 1:N for each user. The query result would be line-based, for each event. Can't you run another query for each user ? – MyICQ May 06 '22 at 10:41
  • How would i do it? – esentai May 06 '22 at 11:08

1 Answers1

0

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.

forpas
  • 160,666
  • 10
  • 38
  • 76
  • thank you. is json_object similar to group_concat in that it is a function that you can pass arguments into? what would be a good source to read more on json_object to see other practical examples? – esentai May 07 '22 at 05:12
  • 1
    @esentai here: https://www.sqlite.org/json1.html you can find more about JSON functions of SQLite. group_concat() is an aggregate function that works with any type of data and returns strings, but what your expected results are structured json data. – forpas May 07 '22 at 06:34