Two queries, one JSON result

Question

I have got two queries, but I just want one result:

1 Query, give me all the data of the column

 SELECT * FROM dbo.Notificacion
 FOR JSON PATH, ROOT('notification')

2 Query give me all the column names

 SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'notificacion' ORDER BY ORDINAL_POSITION
 FOR JSON PATH, ROOT('COLUMN_NAME')

This way, I can make the view of the application more dynamic.

I want JSON something like this:

{
  "COLUMN_NAME": [
    {
      "COLUMN_NAME": "ID"
    },
    {
      "COLUMN_NAME": "Active"
    },
        {
      "COLUMN_NAME": "date"
    },
        {
      "COLUMN_NAME": "dateUpdate"
    }
  ],
    "notification": [
    {
      "ID": 1103,
      "Active": 1016,
      "date": 0,
      "dateUpdate": 0
    },
    {
      "ID": 1103,
      "Active": 1016,
      "date": 0,
      "dateUpdate": 0
    },
        {
      "ID": 1103,
      "Active": 1016,
      "date": 0,
      "dateUpdate": 0
    }
  ]
  
}

Answer 1

This may not be the most optimal way to skin the cat but when I need to combine different JSON results in a single output, I typically use the JSON_QUERY() function.

Here is one way to apply it in your case:

SELECT
  COLUMN_NAMES = JSON_QUERY(inf_sch.json_result)
, Notification = JSON_QUERY(notif  .json_result)
FROM
  (
    SELECT COLUMN_NAME
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_NAME = 'notificacion'
    ORDER BY ORDINAL_POSITION
    FOR JSON PATH
  ) AS inf_sch (json_result)
, (
    SELECT *
    FROM dbo.Notificacion
    FOR JSON PATH
  ) AS notif (json_result)
FOR JSON
  PATH, WITHOUT_ARRAY_WRAPPER

The two derived tables are your two original queries with the ROOT() specifier stripped off. They each produce a JSON output. Each output is essentially injected into the outer level output using the JSON_QUERY function. The function makes sure that its argument is injected as a JSON object rather than an nvarchar string representing one.

The WITHOUT_ARRAY_WRAPPER option at the end of the query makes sure the top-level JSON is returned as a single object rather than an array.

Again, I understand that this may not be the best method. Perhaps a more concise and elegant way could be achieved using the AUTO option of the FOR JSON clause. I admit I am not very familiar with it. Hopefully someone can come along and show both of us, as well as the rest of the audience, how to successfully use it in your scenario.