Fix Error 13681 “A JSON index already exists on column” in SQL Server

If you’re getting SQL Server error 13681 that states “A JSON index already exists on column…” , it’s probably because you’re trying to create a JSON index on a column that already has one. In SQL Server, each JSON column can have no more than one JSON index.

The solution here is to simply stop trying to create a second JSON index. Another option would be to drop the existing one and create the new one.

Example of Error

Suppose we create the following table and JSON index:

CREATE TABLE support_tickets (
    ticket_id   INT     NOT NULL PRIMARY KEY,
    details     JSON    NOT NULL
);

CREATE JSON INDEX ix_tickets_details
    ON support_tickets (details);

That code will run without error.

Now let’s try to create another JSON index:

CREATE JSON INDEX ix_tickets_details_2
    ON support_tickets (details)
    FOR ('$.status', '$.priority', '$.customer_id');

Output:

Msg 13681, Level 16, State 1, Line 1
A JSON index 'ix_tickets_details' already exists on column 'details' on table 'support_tickets', and multiple JSON indexes per column are not allowed.

We get an error because a JSON index already exists on that table.

Solution

If you get this error, you should step back and look at the existing index. Is it preferable to the one you’re trying to create? Or would the new one be better than leaving the existing one? It could be that you’re trying to create a better version of the existing one. Perhaps you’re trying to add a new path to the index. In that case, you could drop the existing index and create the new one:

DROP INDEX ix_tickets_details ON support_tickets;

CREATE JSON INDEX ix_tickets_details
    ON support_tickets (details)
    FOR ('$.status', '$.priority', '$.customer_id');

Output:

Commands completed successfully.

In this case I gave the index the same name as the old one, but you can choose a name that suits your situation.

You may be wondering why we didn’t use the ALTER INDEX statement?

While SQL Server does have an ALTER INDEX statement, it’s used for index maintenance operations (like rebuilding, reorganizing, or disabling) and for changing index-specific options (like FILLFACTOR or compression settings), but not the index definition itself (the columns it covers). If you only need to perform such maintenance, then you might be able to get away with doing an ALTER INDEX operation instead of dropping and creating a new index.