Microsoft SQL Server FOR JSON PATH with column aliases


FOR JSON PATH enables you to control format of the output JSON using column aliases:

SELECT top 3 object_id as id, name as [], type as [data.type]
FROM sys.objects

Column alias will be used as a key name. Dot-separated column aliases ( and data.type) will be generated as nested objects. If two column have the same prefix in dot notation, they will be grouped together in single object (data in this example):

  {"id":3,"data":{"name":"sysrscols","type":"S "}},
  {"id":5,"data":{"name":"sysrowsets","type":"S "}},
  {"id":6,"data":{"name":"sysclones","type":"S "}}