I want to return JSON with tree structure from SQL result

I would like to achieve an API that returns JSON with tree structure from SQL results such as the following example.
Could you please let me know if there is a fast and good way to do it?

■ Assumed Environment
sql —MySQL
languages:python 3.9

SQL

select g.id as groupId, i.id as itemId, i.name as itemName, u.id as userId, u.name as userName 
from group g
left outer join user u on u.group_id=g.id
left router join itemion i.group_id=i.id;

SQL Results

Looking forward to JSON

[
    {
        'groupId': 0,
        'items': [
            {
                'itemId': 0,
                'itemName': 'itemA'
            },
            {
                'itemId': 1,
                'itemName'; 'itemB'
            }
        ]
        'users': [
            {
                'userId': 'aaa',
                'userName': 'userA'
            },
            {
                'userId': 'bbb',
                'userName': 'userB'
            }
        ]
    },
    {
        'groupId': 1,
        'items': [
            {
                'itemId': 0,
                'itemName': 'itemA'
            },
            {
                'itemId': 1,
                'itemName'; 'itemB'
            }
        ]
        'users': [
            {
                'userId': 'aaa',
                'userName': 'userA'
            },
            {
                'userId': 'bbb',
                'userName': 'userB'
            }
        ]
    }
]

python sql json

2022-09-30 14:26

1 Answers

The aggregation function is available in groupby in a package called Toolz that allows you to flexibly aggregate and manipulate json's data.
The json format you asked me about seems to be a proprietary format, so I think it's the right way to use a loop to shape it.

Reference: python how to search a string, count values and group by in json

sample code

import json
import MySQLdb#ToDo: pip install mysql
from toolz import ittertoolz#ToDo: pip install toolz

with MySQLdb.connect(
            user='root',
            host='localhost',
            db = 'mysql',
            charset="utf8") as con, \
     con.cursor(MySQLdb.cursors.DictCursor) as cur:
        US>"sql="" 
select 0 as groupId, 0 as itemId, 'itemA' as itemName, null as userId, null as userName union all
select 0 as groupId, 1 as itemId, 'itemB' as itemName, null as userId, null as userName union all
select 0 as groupId, null as itemId, null as itemName, 'aaa' as userId, 'userA' as userName union all
select 0 as groupId, null as itemId, null as itemName, 'bbb' as userId, 'userB' as userName union all
select1 as groupId, 0 as itemId, 'itemA' as itemName, null as userId, null as userName union all
select 1 as groupId, 1 as itemId, 'itemB' as itemName, null as userId, null as userName union all
select1 as groupId, null as itemId, null as itemName, 'aaa' as userId, 'userA' as userName union all
select1 as groupId, null as itemId, null as itemName, 'bbb' as userId, 'userB' as userName
""" 
        cur.execute(sql)
        rows=cur.fetchall()
        json_rows = json.loads(json.dumps(rows))

        result = [ ]
        # Loop while grouping by groupId
        for g_id, g_value in intertoolz.groupby('groupId',json_rows).items():
            group={'groupId':g_id, 'items':[], 'users':[]}
            # Develop items key items (use only the first item if more than one itemId exists)
            for id, vinintertoolz.groupby('itemId',g_value).items():
                if id is None:
                    continue
                group['items'].append({'itemId':id,'itemName':v[0]['itemName']})
            # Develop user key items (use only the first item if there are multiple identical userId)
            for id, vinintertoolz.groupby('userId',g_value).items():
                if id is None:
                    continue
                group['users'].append({'userId':id,'userName':v[0]['userName']})
            result.append(group)

        print(json.dumps(result,indent=4))

Run Results

[
    {
        "groupId"—0,
        "items": [
            {
                "itemId": 0,
                "itemName": "itemA" 
            },
            {
                "itemId": 1,
                "itemName": "itemB" 
            }
        ],
        "users": [
            {
                "userId": "aaa",
                "userName": "userA" 
            },
            {
                "userId": "bbb",
                "userName": "userB" 
            }
        ]
    },
    {
        "groupId"—1,
        "items": [
            {
                "itemId": 0,
                "itemName": "itemA" 
            },
            {
                "itemId": 1,
                "itemName": "itemB" 
            }
        ],
        "users": [
            {
                "userId": "aaa",
                "userName": "userA" 
            },
            {
                "userId": "bbb",
                "userName": "userB" 
            }
        ]
    }
]

2022-09-30 14:26

If you have any answers or tips

Popular Tags

python x 4647

android x 1593

java x 1494

javascript x 1427

c x 927

c++ x 878

ruby-on-rails x 696

php x 692

python3 x 685

html x 656