clear markdown compare help best fiddles feedback dbanow.uk
clear markdown donate comments/suggestions/bugs a leap of faith? bible365 diddy dollings
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 2555381 fiddles created (37437 in the last week).

CREATE TABLE user ( id INTEGER NOT NULL PRIMARY KEY, name TEXT )
 hidden batch(es)


CREATE TABLE post ( id INTEGER NOT NULL PRIMARY KEY, user_id INTEGER NOT NULL, content TEXT )
 hidden batch(es)


INSERT INTO user(id, name) VALUES (1, 'Vasya'), (2, 'Petya'), (3, 'Slava')
 hidden batch(es)


INSERT INTO post(id, user_id, content) VALUES (1, 1, 'Petya ti petuh'), (2, 1, 'Ne umeesh zaprosi pisat'), (3, 2, 'Net ti')
 hidden batch(es)


SELECT u.id, JSON_OBJECT( 'id', u.id, 'name', u.name, 'posts', IF(COUNT(p.id) = 0, JSON_ARRAY(), JSON_ARRAYAGG(JSON_OBJECT( 'id', p.id, 'content', p.content ))) ) as user FROM user AS u LEFT JOIN post AS p ON p.user_id = u.id GROUP BY u.id
id user
1 {"id": 1, "name": "Vasya", "posts": [{"id": 2, "content": "Ne umeesh zaprosi pisat"}, {"id": 1, "content": "Petya ti petuh"}]}
2 {"id": 2, "name": "Petya", "posts": [{"id": 3, "content": "Net ti"}]}
3 {"id": 3, "name": "Slava", "posts": []}
 hidden batch(es)