Create view with JSON array based on many to many table

Question

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?

You'll need to build this array using string-based approach. Currently, building a JSON array of scalar values is not possible using FOR JSON. What is your SQL Server version? — Zhorov
– Zhorov, Commented Jun 27, 2021 at 20:24
Take a peek at stackoverflow.com/questions/66699084/… Gordon's answer is for 2017+ while my answer will support <2017 — John Cappelletti
– John Cappelletti, Commented Jun 27, 2021 at 20:25
@DaleK OP is looking for a simple ARRAY. Not clear why MS excluded that functionality. — John Cappelletti
– John Cappelletti, Commented Jun 27, 2021 at 20:28
@JohnCappelletti thanks for the reply, but I guess I'll leave the old way. I was hoping it could be done easier. — Krystian Wolański
– Krystian Wolański, Commented Jun 27, 2021 at 20:35
@KrystianWolański Really not so bad. Take a peek at the two options provided — John Cappelletti
– John Cappelletti, Commented Jun 27, 2021 at 20:37

John Cappelletti · Accepted Answer · 2021-06-27 21:00:31Z

1

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]

edited Jun 27, 2021 at 21:00

answered Jun 27, 2021 at 20:37

John Cappelletti

82.3k7 gold badges50 silver badges75 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Krystian Wolański Over a year ago

In my case it will look like this: (SELECT '['+string_agg(BusinessId, ',') +']' FROM [TableA].[dbo].[user_business_entities] WHERE UserId = u.Id) AS BusinessEntityIds

Charlieface Over a year ago

Note that for text you would want '[' + string_agg('"' + string_escape(SomeValue, 'json') + '"', ',') + ']'

Collectives™ on Stack Overflow

Create view with JSON array based on many to many table

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related