How to arrange JSON collections within SQL Server result

March 3, 2024

I’ve been trying for a while now to retrieve the following result from an SQL Server query:

{
  "data":[
    {"id":3,"type":1,"job":1},
    {"id":4,"type":2,"job":34},
  ],
  "collections": {
    "jobs":[
      {"key":1,"label":"Fenchurch"},
      {"key":34,"label":"Raggle"}
    ],
    "users":[
      {"id":5,"label":"Bob"},
      {"id":20,"label":"Jeff"}
    ]
  }
}

My closest two attempts have gotten me this:

{
  "data":[
    {"id":3,"type":1,"job":1},
    {"id":4,"type":2,"job":34},
  ],
  "collections":[{
    "jobs":[
      {"key":1,"label":"Fenchurch"},
      {"key":34,"label":"Raggle"}
    ],
    "users":[
      {"id":5,"label":"Bob"},
      {"id":20,"label":"Jeff"}
    ]
  }]
}

Notice "collections":[{ and closing }] it should just be "collections": { }

I’ve gotten this result with the following SQL:

SELECT 
(
    SELECT
    data = (
        SELECT TOP 2 * 
        FROM Event 
        FOR JSON PATH
    ),
    (
    SELECT 
        (
            SELECT TOP 2 id AS 'key', job_number AS 'label' 
            FROM [Job] 
            ORDER BY job_number DESC 
            FOR JSON PATH
        ) AS 'jobs',
        (
            SELECT TOP 2 id, full_name AS 'label' 
            FROM [User] 
            ORDER BY full_name 
            FOR JSON PATH
        ) AS 'users'
        FOR JSON PATH
    ) AS 'collections'
    FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
) AS 'result'

My other closest attempt was using CONCAT_WS() gave me the following:

{
  "data":[
    {"id":3,"type":1,"job":1},
    {"id":4,"type":2,"job":34},
  ],
  "collections":"
  {
    \"jobs\":[
      {\"key\":1,\"label\":\"Fenchurch\"},
      {\"key\":34,\"label\":\"Raggle\"}
    ]
  },
  {
    \"users\":[
      {\"id\":5,\"label\":\"Bob\"},
      {\"id\":20,\"label\":\"Jeff\"}
    ]
  }"
}

Which enclosed "jobs" and "users" in individual { } rather both being within one set.

The SQL to achieve this is as follows:

SELECT
(
    SELECT
    data = (
        SELECT TOP 2 * 
        FROM Event 
        FOR JSON PATH
    ),
    (
        SELECT CONCAT_WS(',',
            (
                SELECT TOP 2 id AS 'key', job_number AS 'label' 
                FROM [Job] 
                ORDER BY job_number DESC 
                FOR JSON PATH, ROOT('jobs')
            ),
            (
                SELECT TOP 2 id, full_name AS 'label' 
                FROM [User] 
                ORDER BY full_name 
                FOR JSON PATH, ROOT('users')
            )
        )
    ) AS 'collections' FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
) AS 'result'

I started looking at splitting this into multiple queries and then construction my own JSON object, but I wasn’t getting very far so thought it’s about time I call on the help of the community.

An help on this would be greatly appreciated.

Thank you in advance.

>Solution :

You could just add WITHOUT_ARRAY_WRAPPER to the collections subquery, but then you would need to nest it in JSON_QUERY() to prevent double-escaping (because “WITHOUT_ARRAY_WRAPPER` does that).

Instead FOR JSON allows you to specify a nested path, which works well if you have multiple scalar values that you want to combine into a single nested object.

SELECT result = (
    SELECT
      data = (
        SELECT TOP 2 * 
        FROM Event 
        FOR JSON PATH
      ),
      (
        SELECT TOP 2
          j.id AS key,
          j.job_number AS label
        FROM Job j
        ORDER BY
          j.job_number DESC 
        FOR JSON PATH
      ) AS [collections.jobs],
      (
        SELECT TOP 2
          u.id,
          u.full_name AS label
        FROM User u
        ORDER BY
          u.full_name 
        FOR JSON PATH
      ) AS [collections.users]
    FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
);