SQL SERVER – Error Fix: Msg 13601 Working with JSON Structure

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.

SQL SERVER - Error Fix: Msg 13601 Working with JSON Structure JSON-Error-Msg-13601-01

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:

SQL SERVER - Error Fix: Msg 13601 Working with JSON Structure JSON-Error-Msg-13601-02

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)

, ,
Previous Post
Recover Lost Data Using the Transaction Log Files
Next Post
How to Use Zip With SSIS? – Notes from the Field #130

Related Posts

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.

    Reply
  • 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

    Reply
    • FumblesWithCode
      January 17, 2019 4:13 am

      @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.

      Reply
  • 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

    Reply

Leave a Reply

Menu