Building and Grouping JSON Object in SQL Server

Question

I have the following table and I'm trying to combine the rows into JSON objects.

Username	AccessKeys	Marker
user1	{"Account":"1","Checking":"0001","Loan":"null","Savings":0}	New
user2	{"Account":"2","Checking":"0001","Loan":"null","Savings":0}	New
user2	{"Account":"3","Checking":"0001","Loan":"null","Savings":0}	New

The result should look something like this.

Username	JSON
user1	{"Accounts": [{"Account": "1","Checking": "0001","Loan": null,"Savings": 0}],"Marker": "New"}
user2	{"Accounts": [{"Account": "1","Checking": "0001","Loan": null,"Savings": 0},{"Account": "2","Checking": "0001","Loan": null,"Savings": 0}],"Marker": "New"}

My current query is this. I've been able to get this far but not sure how to proceed from here.

SELECT
     Username
    ,Accounts = (
        SELECT
             Account
            ,Checking
            ,Loan
            ,Savings
        FROM dbo.Accounts A1
        WHERE A1.Account= A2.Account
        FOR JSON PATH, WITHOUT_ARRAY_WRAPPER 
     )
FROM
    dbo.Accounts A2
GROUP BY
     Accounts
    ,Username
;

Thanks in advance!

Solution

This is my final query.

SELECT
  Username
 ,(
      SELECT
           Accounts = JSON_QUERY((
           SELECT AK.*
           FROM
                dbo.Accounts A2
                CROSS APPLY OPENJSON(Accounts) WITH (
                      Account nvarchar(10)
                     ,Checking nvarchar(10)
                     ,Loan nvarchar(10)
                     ,Savings int
                ) AK
           WHERE A2.Username = A1.Username
           FOR JSON PATH
           ))
           ,'New' Marker
      FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
 ) JSON
FROM dbo.Accounts A1
GROUP BY Username
;

Answer 1

With SQL Server creating JSON array elements usually involves json_query, such as...

select
  Username,
  [JSON] = (
    select
      [Accounts] = json_query((
        select AK.*
        from dbo.Accounts A2
        cross apply openjson(AccessKeys) with (
          Account nvarchar(10),
          Checking nvarchar(10),
          Loan nvarchar(10),
          Savings int
        ) AK
        where A2.Username = A1.Username
        for json path
      )),
      [Marker]
    for json path, without_array_wrapper
  )
from dbo.Accounts A1
group by Username, Marker;

Which yields the results...

Username	JSON
user1	{"Accounts":[{"Account":"1","Checking":"0001","Loan":"null","Savings":0}],"Marker":"New"}
user2	{"Accounts":[{"Account":"2","Checking":"0001","Loan":"null","Savings":0},{"Account":"3","Checking":"0001","Loan":"null","Savings":0}],"Marker":"New"}