

以下の例ようなSQLの結果からツリー構造をもつJSONを返すようなAPIを実現したいです。  
なるべく高速でうまい方法があれば教えていただけないでしょうか。

■想定環境  
sql: MySQL  
言語：python3.9

## 例

### SQL

[code]

    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 outer join item i on i.group_id=i.id;
    
[/code]

### SQL結果

groupId | itemId | itemName | userId | userName  
---|---|---|---|---  
0 | 0 | itemA | null | null  
0 | 1 | itemB | null | null  
0 | null | null | aaa | userA  
0 | null | null | bbb | userB  
1 | 0 | itemA | null | null  
1 | 1 | itemB | null | null  
1 | null | null | aaa | userA  
1 | null | null | bbb | userB  
  
### 期待するJSON

[code]

    [
        {
            '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'
                }
            ]
        }
    ]                  
    
[/code]


CC BY-SA 4.0
2022-01-06T12:39:36.043
85527
2022-01-07T05:55:16.843
12962
question
python sql json
SQL結果からツリー構造をもつJSONを返したい


jsonのデータを柔軟に集計や操作できるToolzというパッケージの[groupby](https://toolz.readthedocs.io/en/latest/api.html#toolz.itertoolz.groupby)で集約関数を利用できます。  
ご質問のjsonフォーマットは独自形式のようですので、あとはループを使用して整形していく方法が正道のように思います。

参考資料: [python how to search a string, count values and group by in
json](https://stackoverflow.com/a/60695373)

**サンプルコード**

[code]

    import json
    import MySQLdb  # ToDo: pip install mysql
    from toolz import itertoolz # ToDo: pip install toolz
    
    with MySQLdb.connect(
                user='root',
                host='localhost',
                db='mysql',
                charset="utf8") as con, \
         con.cursor(MySQLdb.cursors.DictCursor) as cur:
            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
    select 1 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
    select 1 as groupId, null as itemId, null    as itemName, 'aaa' as userId, 'userA' as userName union all
    select 1 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 = []
            # groupIdごとにグループ化しながらループ処理を行う
            for g_id, g_value in itertoolz.groupby('groupId', json_rows).items():
                group = {'groupId':g_id, 'items':[], 'users':[]}
                # itemsキー項目を整備する(同一のitemIdが複数存在する場合は先頭の項目のみ使用する)
                for id, v in itertoolz.groupby('itemId', g_value).items():
                    if id is None:
                        continue
                    group['items'].append({'itemId':id,'itemName':v[0]['itemName']})
                # usersキー項目を整備する(同一のuserIdが複数存在する場合は先頭の項目のみ使用する)
                for id, v in itertoolz.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))
    
[/code]

**実行結果**

[code]

    [
        {
            "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" 
                }
            ]
        }
    ]
    
[/code]


CC BY-SA 4.0
2022-01-07T05:55:16.843
85544
2022-01-07T05:55:16.843
9820
85527
answer