SQL to retrieve multidimensional Array

Question

I'm trying to learn SQL but hitting a wall with the following problem. I can solve it easily using imperative programming but would like to know if it's possible to solve this problem using just a query:

Input

Table 1 (users)

ID  | Firstname
--------------
1   | Felix
2   | Michael
3   | Tobias

Table 2 (hobbies)

ID  | Hobby     | User
------------------------
1   | cooking   | 1
2   | cat       | 1
3   | piano     | 2

Wanted Output

{
    "users": [{
            "firstname": "Felix",
            "hobbies": [{
                "id": 1,
                "name": "cooking"
            }, {
                "id": 2,
                "name": "cat"
            }]
        },
        {
            "firstname": "Michael",
            "hobbies": [{
                "id": 3,
                "name": "piano"
            }]
        },
        {
            "firstname": "Tobias",
            "hobbies": []
        }
    ]
}

Doesn't need to be directly JSON of course. With joins I've come so far that either a row is created for each hobby x user (two rows for Felix)

Felix | cooking
Felix | cat
Michael | piano

or that some information got lost (Felix' cat got lost)

Felix | cooking
Michael | piano

Take a look at the MS Docs here learn.microsoft.com/en-us/sql/relational-databases/json/… if you have SQL Server 2016 — Steve Ford
– Steve Ford, Commented May 9, 2017 at 11:38
Quite easy with Postgres: rextester.com/SQA15827 don't know about MySQL — user330315
– user330315, Commented May 9, 2017 at 11:49

Steve Lovell · Accepted Answer · 2017-05-09 20:14:32Z

2

Second Update

I don't like this it feels too much like "brute force", and I guess there is a more elegant way ...

select concat('{ "users": [', group_concat(json.hobbies), '] }') as hobbies
from
(
    select concat('{"firstname": "',u.firstname,'", "hobbies": [', group_concat(json_object('id',h.id,'name',h.hobby),''), ']}') hobbies
    from users u
        left join hobbies h on u.id = h.user
    group by u.id
    order by u.firstname
) as json

Output

{ "users": [{"firstname": "Felix", "hobbies": [{"id": 1, "name": "cooking"},{"id": 2, "name": "cat"}]},{"firstname": "Michael", "hobbies": [{"id": 3, "name": "piano"}]},{"firstname": "Tobias", "hobbies": [{"id": null, "name": null}]}] }

First Update

I've lost my battle with MySQL JSON (for now). The closest I could get was the following, which I guess might still be of some use:

select u.firstname, group_concat(json_object('id',h.id,'name',h.hobby),'') hobbies
from users u
left join hobbies h on u.id = h.user
group by u.id
order by u.firstname

Output

+-----------+-------------------------------------------------------+
| firstname | hobbies                                               | 
+-----------+-------------------------------------------------------+
| Felix     | {"id": 1, "name": "cooking"},{"id": 2, "name": "cat"} |
| Michael   | {"id": 3, "name": "piano"}                            |
| Tobias    | {"id": null, "name": null}                            | 
+-----------+-------------------------------------------------------+

Original Answer

Not sure about JSON, will this suffice for the SQL side?

select
    u.id,
    u.firstname,
    group_concat(h.hobby ORDER BY h.ID SEPARATOR ',') as hobbies
from
    my_users u
    LEFT JOIN hobbies h ON
    u.ID = h.user
group by
    u.id,
    u.firstname

Output

+----+-----------+-------------+
| id | firstname |   hobbies   |
+----+-----------+-------------+    
|  1 | Felix     | cooking,cat |
|  2 | Michael   | piano       |
|  3 | Tobias    | NULL        |
+----+-----------+-------------+

edited May 9, 2017 at 20:14

answered May 9, 2017 at 12:24

Steve Lovell

2,5742 gold badges15 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Felix Over a year ago

I like the idea, but I need the ID sadly

Steve Lovell Over a year ago

It could be added in with the hobbies without difficulty, to look (e.g.) like this: 1: cooking, 2: cat. If that's good enough, I'll make the edit. But in the meantime, I'll see if I can find a full JSON output. I like learning this stuff, so ...

Steve Lovell Over a year ago

Not too sure under what circumstances you get notified, but in case you didn't already see, I've made an attempt at getting full JSON output from MySQL. It seems to work well, though I'd be surprised be surprised if there aren't more elegant ways to do it. Will be interested in your feedback.

Felix Over a year ago

Indeed I did not get notified about the edit of your answer. For my task this solution works perfectly fine. The question I have now is if it's better to build such unsual queries as you call them "brute force" or to manipulate the data in the retrieving code

Steve Lovell Over a year ago

Oh, I'd definitely use it if it works. But it will be worth keeping an eye out for other later answers to your question in case anyone has improvements. Alternatively, you could ask a new question specifically asking for improvements to this code. I attempted a solution closer to the one that @a_horse_with_no_name supplied, but it kept escaping parts of the inner JSON objects, and I could make neither head nor tail of the MySQL documentation on its other JSON functions. But others will have more experience and know whether there is a better way. In the meantime keep your other code lean.

Collectives™ on Stack Overflow

SQL to retrieve multidimensional Array

1 Answer 1

Second Update

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Second Update

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related