Building and Grouping JSON Object in SQL Server

user_6701356 0
Viewed 610

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
;
sql-server-2016 tsql sql-server sql json
2 Answers

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"}
user_3901220

You can use ROOT to add a root property, and remove WITHOUT_ARRAY_WRAPPER becaus eyou actually want the array here.

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

This does have the downside of re-querying the table. To improve performance, you could construct the JSON manually using STRING_AGG (SQL Server does not have JSON_AGG unfortunately).

SELECT
     Username
    ,JSON = (
       SELECT Accounts = JSON_QUERY('[' + STRING_AGG(
         (SELECT
             A.Account
            ,A.Checking
            ,A.Loan
            ,A.Savings
         FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
        ), ',') + ']')
       FOR JSON PATH)
FROM
    dbo.Accounts A
GROUP BY
     A.Username;
user_148689970
Related