As you new versions of SQL Server come, the amount of capabilities just increases exponentially. It is tough to keep up in pace with the innovations and learning that one needs to go through. I have in the past written few articles around working with JSON over the blogs earlier. These games of playing with the new capabilities will show up tons of errors as we are not completely aware of what is possible. These experiments lead you from learning to another.
I a recent play with SQL for one of the blogs earlier, I had got an error which I had forgotten to write here. The error which I was getting is shown below:
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.
On first note, it was self-explanatory to what is the correction for the same. I am just going to rehash the steps you need to be aware when working with this error:
- Two columns or path aliases in the query with FOR JSON PATH clause have the same name or one is a prefix of another.
- A JSON formatter cannot decide whether the prefix should reference scalar or object.
Fix JSON Error
A simple resolution for this would be to change the prefix to something like this as shown below:
SELECT 1 as 'a', 2 as 'b' FOR JSON PATH
With that small change being made. The output would get easily resolved and as shown below:
Do keep sharing your errors when working with JSON, I would surely love to post some of these in the blog. On the contrary, are you working with this feature?
Reference: Pinal Dave (https://blog.sqlauthority.com)