MySQL json_object and json_arrayagg with group by not working as expected

Question

I've a many to many relationship with this tables

Table assets:

id	name	other fields
19	asset 1	...
20	asset 2	...
...	...	...

Branches table:

id	name	other fields
1	branch 1	...
2	branch 2	...
...	...	...

branch_r_asset:

branch_id	asset_id
1	1
1	2
2	14
n	n

I want to return a json object in this form:

{
  'branches' : [
    {
      'branch_id': 1,
      'branch_name': 'branch 1',
      'branch_children': [
        {
          'asset_id': 1,
          'asset_name': 'asset 1'
        },
        {
          'asset_id': 2,
          'asset_name': 'asset 2'
        },
        {
          'asset_id': 99999,
          'asset_name': 'asset 99999'
        }
      ]
    },
    {
      'branch_id': 2,
      'branch_name': 'branch 2',
      'branch_children': [
        {
          'asset_id': 1,
          'asset_name': 'asset 1'
        },
        {
          'asset_id': 2,
          'asset_name': 'asset 2'
        },
        {
          'asset_id': 99999,
          'asset_name': 'asset 99999'
        }
      ]
    }
  ]
}

The children could be arrays or objects. It's indistinct for the example.

With this script:

select json_object(
  'branch_id', branches.id,
  'branch_name', branches.name,
  'branch_children', json_arrayagg(json_object('asset_id', list.asset_id, 'asset_name', list.asset_name))
)
from branches
left join (
  select b.id as branch_id, a.id as asset_id, a.name as asset_name
  from branch_r_asset ba
  join branches b
    on b.id = ba.branch_id
  join assets a
    on a.id = ba.asset_id
  group by b.id
) list on list.branch_id = branches.id
group by branches.id;

I'm getting the results but separated in rows for each branch_id I have in the table like this:

{"branch_name": "branch 1", "branch_id": "1", "branch_children": [{"asset_name": "asset 1", "asset_id": "1" }, {"asset_name": "asset 2", "asset_id": "2" }]}

{"branch_name": "branch 2", "branch_id": "2", "branch_children": [{"asset_name": "asset 15", "asset_id": "15" }, {"asset_name": "asset 244", "asset_id": "244" }, {"asset_name": "asset 7", "asset_id": "7" }]}

{"branch_name": "branch 3", "branch_id": "3", "branch_children": [{"asset_name": "asset 12", "asset_id": "12" }}]}

I'm not sure if it's json managing problem (by me) or a group function.

Thanks in advance.

Answer 1

select json_object(
  'branches', json_arrayagg(
    json_object(
      'branch_id', branch_id,
      'branch_name', branch_name,
      'branch_children', branch_children)
    )
  )
)
from (
  select b.id as branch_id, b.name as branch_name, 
    json_arrayagg(
      json_object(
        'asset_id', a.id, 
        'asset_name', a.name
      )
    ) as branch_children
  from branches b
  left join branch_r_asset ba
    on b.id = ba.branch_id
  left join assets a
    on a.id = ba.asset_id
  group by b.id
) as t