Two queries, one JSON result

All we need is an easy explanation of the problem, so here it is.

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
    }
  ]
  
}

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 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.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply