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)
4 Comments. Leave new
Are you kidding me? This is the equivalent of “I get an error when I do X” and your response “so don’t do X!”. Useless.
Hi Pinal,
I have an answer to to this problem that differs to yours and might provide your users some help. The reason that the code does not work is due to malformed JSON that will cause overwrite of the data. ‘a’ and ‘a.b’ are both seen as object ‘a’ and is blocked by the compiler. What will work is:
SELECT 1 as ‘a.a’, 2 as ‘a.b’, 3 as ‘b’, 4 as ‘c.a’
FOR JSON PATH
This simply ensures that all the a pairs are together for object ‘a’ and then you can continue to add to your JSON structure.
The other instance where this error occurs is if you have your object pairs not in sequence. Here is an example:
SELECT 1 as ‘a.a’, 3 as ‘b’, 2 as ‘a.b’, 4 as ‘c.a’
FOR JSON PATH
‘a.a’ and ‘a.b’ are separated by ‘b’. In processing ‘a.b’ it would overwrite the value of ‘a.a’ which is blocked by the For JSON function. Make sure all your pairs are sequential when creating JSON in SSMS or you will have this issue.
Final example of valid JSON created using FOR JSON PATH
SELECT 1 as ‘a.a’, 2 as ‘a.b’, 3 as ‘b’, 4 as ‘c.a’, 5 as ‘d.a’, 6 as ‘d.b.a’, 7 as ‘d.b.b’, 8 as ‘d.c’
FOR JSON PATH
@Beau Thank you for your comment. I recently encountered this error after I re-ordered several object pairs. I suspected the sequence was the issue. Your comments are the first I’ve seen on the issue.
Hi Pinal,
Could you please help me with below JSON ask in SQL Server?
I have a JSON with below structure.
{
“Class”:”First Grade”,
“Students”:
[
{“Name”:”Student1″, “Age”:”6″, “DOB”:”2012-01-01″,”Gender”:”Male”},
{“Name”:”Student2″, “Age”:”7″},
{“Name”:”Student3″, “Age”:”6″, “DOB”:”2012-04-13″}
]
}
I would like to get the result like below
Class | Student Name | Student Details
—————–|———————–|——————-
First Grade | Student 1 | {“Name”:”Student1″, “Age”:”6″, “DOB”:”2012-01-01″,”Gender”:”Male”}
First Grade | Student 2 | {“Name”:”Student2″, “Age”:”7″}
First Grade | Student 3 | {“Name”:”Student3″, “Age”:”6″, “DOB”:”2012-04-13″}
Here, the elements in Students array is not constant. I just need to read whatever comes in, and store it against Student Name.
Appreciate your help with this.
Thanks,
Sreeraj