On nodeJs, can SQL Server return joined tables an array of objects?

Question

My table data is:

                 **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      |
    |------------|------------|------------|------------|

My query is

SELECT *
FROM partMaster
left join boms on boms.partId = partMaster.id
WHERE (partMaster.id = '01')

This returns (along with other things) a recordset, such as,

[
    {
        id:[01,01],
        partNumber:'ASSY1'
        rev:'C',
        itemId:'02',
        qty:5
    }
,
   {
        id:[01,01],
        partNumber:'ASSY1'
        rev:'C',
        itemId:'03',
        qty:11
    }
]

This to me is very confusing. I would prefer something like,

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

Is this possible with SQL Server (or any NodeJs Compatible SQL database)?

Answer 1

If you're using SQL Server 2016 or newer you can use FOR JSON.

SELECT
    p.id,
    p.partNumber,
    p.rev,
    (SELECT b.id, b.partId, b.itemId, b.qty FOR JSON PATH) as boms
FROM partMaster p
left join boms b on b.partId = p.id
WHERE (p.id = '01');

Side note: it is best practice to never use SELECT * in your code. Always return only what you need in your resultset. Table schemas can change over time which makes the results of SELECT * also vary.