As new versions of SQL Server are released, the capabilities of the platform continue to expand at an exponential rate. Keeping up with these innovations and the learning required can be quite challenging. In the past, I have written a few articles about working with JSON in SQL Server, and through these experiments, I have encountered various errors that have helped me learn and grow.

Recently, while working on a blog post, I came across an error that I forgot to mention. The error message I received was:

Msg 13601, Level 16, State 1, Line 1 Property 'a.b' 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.

At first glance, the error message provided a clear indication of what needed to be corrected. Allow me to summarize the steps you should be aware of when encountering this error:

When using the FOR JSON PATH clause in a query, if two columns or path aliases have the same name or if one is a prefix of another, a conflict arises. The JSON formatter cannot determine whether the prefix should reference a scalar or an object.

To resolve this error, a simple solution would be to change the prefix to something unique. For example:

SELECT 1 as 'a', 2 as 'b'
FOR JSON PATH

By making this small change, the output will be resolved, as shown below:

[
  {
    "a": 1,
    "b": 2
  }
]

I encourage you to share any errors you encounter while working with JSON. I would love to feature some of these in future blog posts. On the contrary, are you currently exploring this feature in SQL Server? Let me know in the comments!