I have a users table and a notes table and I am trying to figure out the best way forward so I can edit the data easily and show it on my front-end. I was considering storing the notes as JSON in the notes table but figured that may be harder to edit say the title of a note later.
CREATE TABLE users(id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT UNIQUE)
CREATE TABLE notes(id INTEGER PRIMARY KEY AUTOINCREMENT, content TEXT UNIQUE, user_id FOREIGN KEY(user_id) REFERENCES users(id))
INSERT INTO users(name) VALUES('John')
INSERT INTO users(name) VALUES('Jane')
INSERT INTO notes(user_id, content) VALUES(1, "John's 1st Note")
INSERT INTO notes(user_id, content) VALUES(1, "John's 2nd Note")
INSERT INTO notes(user_id, content) VALUES(2, "Jane's 1st Note")
I am trying to write a select statement that will return all of my users and group their notes together as JSON. Something like this returned would be awesome:
[name:John,notes:[{id:1, content:"John's 1st Note"},{id:2, content:"John's 2nd Note"}],
name: Jane,notes: [{id:3, content:"Jane's 1st Note"}]]
I've tried looking at group_concat() and json_group_array() but I'm really struggling trying to get this data together.
SELECT
users.name,
json_group_array(json_object('id', notes.id, 'content', notes.content)) AS notes
FROM
users
LEFT JOIN
notes ON users.id = notes.user_id
GROUP BY
users.id;