How to put JSON into a column data if sub-query returns more than 1 row in MySQL

Question

I want to select portfolio of user with the same query that i used for select users.

Here the example that i want.

User Table

----------------------------------------------------------------------
UID     NAME        USERNAME        EMAIL       PASSWORD        STATUS
----------------------------------------------------------------------
1       Manoj       manoj           [email protected]     ********        1
2       Test U      testing         [email protected]     ********        1
3       Company     user            [email protected]     ********        1
4       Agency      company         [email protected]     ********        1

User table in my database

Portfolio Table

-----------------------------------
PID     UID     TITLE       STATUS
-----------------------------------
1       1       title 1     1
2       1       title 2     1
3       1       title 3     1
4       2       title 1     1

Portfolio Table in my database Result I want

----------------------------------
UID     USERNAME        PORTFOLIO
----------------------------------
1       manoj           JSON OBJECT OF PID (1,2,3)
2       testing         JOSN OBJECT OF PID (4)
3       user            NULL
4       company         NULL

Currently I'm trying to use

SELECT u.uid,u.username,(SELECT * FROM portfolio p WHERE u.UID=p.UID) as portfolio FROM users u

Is there any solution for this problem?

Answer 1

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 portfolios
FROM user u
LEFT JOIN portfolio p
  ON u.uid=p.uid
WHERE p.status = 1
GROUP 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 portfolios
FROM user u 
LEFT JOIN portfolio p 
  ON u.uid=p.uid AND p.status=1
GROUP 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                                                                                                                         |
+------+----------+------------------------------------------------------------------------------------------------------------------------------+

Answer 2

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 portfolios
FROM user AS u
LEFT JOIN portfolio AS p ON u.user_id = p.user_id AND p.status = 1
GROUP 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                                                                                                                     |
+---------+----------+--------------------------------------------------------------------------------------------------------------------------+