Bug in For JSON PATH

Question

I have a pretty simple query to run in Azure SQL:

----------

SELECT TOP 10
TABLE_CATALOG AS 'Movement.Something.LocationName'
, TABLE_SCHEMA AS 'Movement.Transporter.Id'
, TABLE_NAME AS 'Movement.Something.Destination'
, COLUMN_NAME AS 'Movement.Something.LocationId'
FROM INFORMATION_SCHEMA.COLUMNS
FOR JSON PATH, WITHOUT_ARRAY_WRAPPER

----------

As you can see, the aliased columns all have unique names. However I get this error:

Property 'Movement.Something.Destination' cannot be generated in JSON output due to a conflict with another column name or alias. Use different names and aliases for each column in SELECT list.

Have I hit a keyword here or something?

Answer 1

Hi @Anonymous ,

From the comments on this article there seems to be the same error, it need to make sure all pairs are sequential when creating JSON in SSMS, to ensure that all the pairs with the same object are together

https://blog.sqlauthority.com/2016/06/29/sql-server-error-fix-msg-13601-working-json-structure/

Now that you are solved your own problem, please do not forget to mark as accepted answer so it can be helpful for other community members with same questions.

-------------

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

I have figured out why this is, SQL cannot handle the order being wrong for JSON PATH expressions, but gives back a poor error response. This query works:

SELECT TOP 10
TABLE_CATALOG AS 'Movement.Something.LocationName'
, TABLE_NAME AS 'Movement.Something.Destination'
, COLUMN_NAME AS 'Movement.Something.LocationId'
, TABLE_SCHEMA AS 'Movement.Transporter.Id'
FROM INFORMATION_SCHEMA.COLUMNS
FOR JSON PATH, WITHOUT_ARRAY_WRAPPER

Could you give back a better error like "When using JSON PATH expressions, columns containing similar path expressions separated by periods need to be queried in consecutive groups"

Answer 3

I encourage you to report this on https://feedback.azure.com/d365community/forum/04fe6ee0-3b25-ec11-b6e6-000d3a4f0da0, so that Microsoft gets to know about this.

Please share the link for the feedback item here!