Format JSON as nested arrays based on id in SQL

Question

I have a stored procedure in SQLS erver that gives the output in the following JSON format:

[
   {
  "accountid":"12312312",
  "Id":1,
  "name":"Ace Signs Ltd"
   },
   {
  "accountid":"213123123",
  "Id":2,
  "name":"Workshare Technology"
   },
  {
  "accountid":"12312312",
  "Id":1,
  "name":"Ace Signs Ltd"
  },
  {
  "accountid":"123123123",
  "Id":2,
  "name":"Workshare"
   }
]

But I want them grouped into nested arrays based on the ID as shown below. Where the ones with the same ID are one tuple.

   {
   "match":[
      [
        {
        "accountid":"12312312",
        "Id":1,
        "name":"Ace Signs Ltd"
        },
        {
        "accountid":"12312312",
        "Id":1,
        "name":"Ace Signs Ltd"
        }
      ],
      [
        {
        "accountid":"12312312",
        "Id":2,
        "name":"Workshare Technology"
        },
        {
        "accountid":"213123123",
        "Id":2,
        "name":"Workshare"
        }
       ]
      ]
    }

The part of the stored procedure that generates the JSON is as follows:

SELECT *
FROM TestJsonFormat 
FOR JSON AUTO

Sample data:

CREATE TABLE TestJsonFormat (
    accountid varchar(255), 
    Id int,
    name varchar(255)
)

insert into TestJsonFormat values(12312312,1,'Ace Signs Ltd')
insert into TestJsonFormat values(213123123,2,'Workshare Technology')
insert into TestJsonFormat values(12312312,1,'Ace Signs Ltd')
insert into TestJsonFormat values(123123123,2,'Workshare')

JSON has no tuples, only arrays and objects. What you posted is a nested array with the objects — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Sep 9, 2020 at 10:29
@Sindu_ It's great. What is your SQL Server version? One possible approach is based on FOR JSON AUTO and basic string aggregation. Also, note that your expected output is not a valid JSON. — Zhorov
– Zhorov, Commented Sep 9, 2020 at 10:45

Zhorov · Accepted Answer · 2020-09-09 11:14:32Z

One possible approach to generate the expected JSON output is a combination from FOR JSON AUTO and basic string aggregation. The following example demostrates this:

Table:

CREATE TABLE TestJsonFormat (
    accountid varchar(255), 
    Id int,
    name varchar(255)
)
insert into TestJsonFormat values(12312312,1,'Ace Signs Ltd')
insert into TestJsonFormat values(213123123,2,'Workshare Technology')
insert into TestJsonFormat values(12312312,1,'Ace Signs Ltd')
insert into TestJsonFormat values(123123123,2,'Workshare')

Statement:

SELECT CONCAT('{"match": [', STRING_AGG(json, ','), ']}')
FROM (
   SELECT DiSTINCT t.id, j.json
   FROM TestJsonFormat t
   CROSS APPLY (
      SELECT accountId, id, name
      FROM TestJsonFormat 
      WHERE id = t.id
      FOR JSON AUTO
   ) j (json)
) cte

Result (formatted):

{
  "match": [
    [
      {
        "accountId":"12312312",
        "id":1,
        "name":"Ace Signs Ltd"
      },
      {
        "accountId":"12312312",
        "id":1,
        "name":"Ace Signs Ltd"
      }
    ],
    [
      {
        "accountId":"213123123",
        "id":2,
        "name":"Workshare Technology"
      },
      {
        "accountId":"123123123",
        "id":2,
        "name":"Workshare"
      }
    ]
  ]
}

Collectives™ on Stack Overflow

Format JSON as nested arrays based on id in SQL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related