21

How can I change the column name "JSON_F52......" to Any(e.g. SalesOrder)

enter image description here

Furqan Misarwala
  • 1,743
  • 6
  • 26
  • 53

2 Answers2

56

Wrap your json building SELECT in another SELECT:

SELECT (
      SELECT SalesOrderNumber AS 'Order.Number',
             OrderDate AS 'Order.Date'
        FROM Sales.SalesOrderHeader
         FOR JSON PATH
) AS SalesOrder
Michal D.
  • 751
  • 6
  • 7
  • Which of these answers do you think is faster? – Jose Sep 17 '18 at 23:59
  • This is the method I use. I arrived here looking for a possible alternative. – critical_error Dec 31 '18 at 16:35
  • This method is faster than `Declare` Because no need use set data into variable. but change json type as object to string. – Amirhossein Jul 18 '20 at 10:34
  • Yep this solution does the trick for me too, saves me having to write out a load of JSON logic in my code first (still supporting ASP code) – Hyperjase Aug 27 '20 at 13:08
  • I would advise caution with this method, it can have a performance impact when fetching large results. "FOR JSON" usually splits long results into several rows of about ~2300 characters each, but I tried wrapping it like this today and it returned a single row of ~30 000 characters, seemingly causing a jump from ~600ms to ~6s – Pabloski GL Jul 26 '23 at 09:49
9

i think the final result will not have anything with that name, it is a temporary name to store the result..

If you want the result in a variable The output of the FOR JSON clause is of type NVARCHAR(MAX), so you can assign it to any variable, as shown in the following example.

DECLARE @SalesOrder NVARCHAR(MAX) = (SELECT TOP 10 * FROM Sales.SalesOrderHeader FOR JSON AUTO)

Then select from @SalesOrder

If you want to store it in a file then check this link

Julian
  • 33,915
  • 22
  • 119
  • 174
AnouarZ
  • 1,087
  • 8
  • 23
  • While this works to remove the column name (`JSON_F52......`), your JSON results are left with an empty string as an index key `"": []` which is bad practice. Notably, this will break the dot-notation property accessor in JavaScript. The better solution, in my opinion, is to wrap the `SELECT` inside of another `SELECT` as described by `Michal D` below. – Crayons Jul 31 '19 at 15:43