How to put JSON into a column data if sub-query returns more than 1 row in MySQL
A bit convoluted, but you could create JSON objects for each row, concatenate them using GROUP_CONCAT and cast the result (wrapped in [] to make it an array) to JSON;
SELECT u.uid, u.username, CASE WHEN p.uid IS NULL THEN NULL ELSE CAST(CONCAT('[', GROUP_CONCAT(JSON_OBJECT('pid', p.pid, 'title', p.title, 'status', p.status)), ']') AS JSON) END portfoliosFROM user uLEFT JOIN portfolio p ON u.uid=p.uidWHERE p.status = 1GROUP BY u.uid, u.username;...which gives...
+------+----------+-----------------------------------------------------------------------------------------------------------------------------------------+| 1 | manoj | [{"pid": 1, "title": "title 1", "status": 1}, {"pid": 2, "title": "title 2", "status": 1}, {"pid": 3, "title": "title 3", "status": 1}] || 2 | testing | [{"pid": 4, "title": "title 1", "status": 1}] || 3 | user | NULL || 4 | company | NULL |+------+----------+-----------------------------------------------------------------------------------------------------------------------------------------+If you're using an older MySQL without JSON support, you could build it as a string;
SELECT u.uid, u.username, CASE WHEN p.uid IS NULL THEN NULL ELSE CONCAT('[', GROUP_CONCAT(CONCAT('{ "pid":',p.pid,',"title":"', REPLACE(p.title, '"', '\\"'), '","status":',p.status, ' }')), ']') END portfoliosFROM user u LEFT JOIN portfolio p ON u.uid=p.uid AND p.status=1GROUP BY u.uid, u.username;...which will give you...
+------+----------+------------------------------------------------------------------------------------------------------------------------------+| uid | username | portfolios |+------+----------+------------------------------------------------------------------------------------------------------------------------------+| 1 | manoj | [{ "pid":2,"title":"title 2","status":1 },{ "pid":3,"title":"title 3","status":1 },{ "pid":1,"title":"title 1","status":1 }] || 2 | testing | [{ "pid":4,"title":"title 1","status":1 }] || 3 | user | NULL || 4 | company | NULL |+------+----------+------------------------------------------------------------------------------------------------------------------------------+
MySQL 8: With JSON_OBJECTAGG and JSON_OBJECT functions
SELECT u.user_id, u.username, CASE WHEN p.user_id IS NULL THEN NULL ELSE JSON_OBJECTAGG( IF(p.portfolio_id IS NULL, '', p.portfolio_id ), JSON_OBJECT('title', p.title, 'status', p.status)) END portfoliosFROM user AS uLEFT JOIN portfolio AS p ON u.user_id = p.user_id AND p.status = 1GROUP BY u.user_id;Result:
+---------+----------+--------------------------------------------------------------------------------------------------------------------------+| user_id | username | portfolios |+---------+----------+--------------------------------------------------------------------------------------------------------------------------+| 1 | manoj | {"1": {"title": "title 1", "status": 1}, "2": {"title": "title 2", "status": 1}, "3": {"title": "title 3", "status": 1}} || 2 | testing | {"4": {"title": "title 1", "status": 1}} || 3 | user | NULL || 4 | company | NULL |+---------+----------+--------------------------------------------------------------------------------------------------------------------------+