Можно ли вернуть массив из обьектов в sql?

Question

Из технологий используется: MariaDB 10.3.14 promise-mysql: "^3.3.1"

Вот примерная таблица с данными:

             **partMaster**

     id        partNumber      rev     
|------------|------------|------------|
|    01      | assy1      |    C       |
|------------|------------|------------|
|    02      | comp1      |    A       |
|------------|------------|------------|
|    03      | comp2      |    F       |
|------------|------------|------------|

                **boms**

     id        partId         itemId        qty     
|------------|------------|------------|------------|
|    01      | 01         |    02      |    5       |
|------------|------------|------------|------------|
|    02      | 01         |    03      |    11      |
|------------|------------|------------|------------|

Мой пример sql кода:

SELECT a.id, a.partNumber, a.rev 
FROM partNumber a 
LEFT JOIN 
boms b 
ON a.id=b.id

Возможно ли получить поле boms со всеми данными как ниже?

[{
id:01,
partNumber:'assy1',
rev:'c'
boms:[{id:01,partId:01,itemId:02,qty:5},{id:02,partId:01,itemId:03,qty:11}] //Left Joined Table 
}]

Answer 1

Во второй таблице мне кажется, что столбец partId является внешним ключом на таблицу partMaster, поэтому запрос выводящий ровно то, что вы хотите, выглядит как-то так:

select json_array(json_object(
    'id', a.id, 'partNumber', a.partNumber, 'rev', a.rev,
    'boms', json_arrayagg(json_object(
        'id', b.id, 'partId', b.partId, 
        'itemId', b.itemId, 'qty', b.qty
    )))
) from partMaster a
inner join boms b on a.id = b.partId
group by a.id

Вывод:

[
    {
        "id": 1,
        "rev": "C",
        "boms": [
            {
                "id": 1,
                "qty": 5,
                "itemId": 2,
                "partId": 1
            },
            {
                "id": 2,
                "qty": 11,
                "itemId": 3,
                "partId": 1
            }
        ],
        "partNumber": "assy1"
    }
]

оставлю код для postgres, т.к. не пользовался MariaDB и заменил функции для работы с json просто по документации, не выполняя сам запрос, в postgres все работает:

select jsonb_build_array(jsonb_build_object(
    'id', a.id, 'partNumber', a.partNumber, 'rev', a.rev,
    'boms', jsonb_agg(jsonb_build_object(
        'id', b.id, 'partId', b.partId, 
        'itemId', b.itemId, 'qty', b.qty
    )))
) from partMaster a
inner join boms b on a.id = b.partId
group by a.id

БЛОГ НА HUSL

Можно ли вернуть массив из обьектов в sql?

Ответы (1 шт):