Create view with JSON array based on many to many table

I have a typical table with users. I have also a many to many table where first column is UserId and second BusinessId. I want to create a view with users where their businessId will be as json.

I tried something like this:

SELECT 
    ISNULL(CAST(u.[Id] AS INT), 0) AS [Id]
             
    ,(SELECT BusinessId FROM [TableA].[dbo].[user_business_entities] WHERE UserId = u.Id FOR JSON AUTO) AS BusinessEntityIds
FROM 
    [TableA].[dbo].[core_users] u

But in view I get this:

Id	BusinessEntityIds
1	[{“BusinessId”:1925},{“BusinessId”:1926}]
2	[{“BusinessId”:15}]

It’s pretty good, but it would be best if json had only values, no key name i.e only ids without “BusinessId”:

Id	BusinessEntityIds
1	[1925, 1926]
2	[15]

How can I do this?

Answer

Two quick options: First is for <=2016 and the 2nd is 2017+

Never understood why MS never provided this functionality of a simple ARRAY.

Option 1 <=2016

Select ID
      ,BusinessEntityIds = '['+stuff((Select concat(',',BusinessEntityIds)  
                                       From  YourTable 
                                       Where ID=A.ID
                                       For XML Path ('')),1,1,'')+']'

    From  YourTable A
    Group By ID

Option 2 2017+

select ID,
       BusinessEntityIds = '['+string_agg(BusinessEntityIds, ',') +']'
from YourTable
group by ID

Both Results Are

ID  BusinessEntityIds
1   [1925,1926]
2   [15]