JSON Index Delete
Introduction
The JSON Index Delete is not really an actual operator. You can encounter it in graphical execution plans in SSMS (and other tools), but if you look at the underlying XML of the execution plan, you will see that it is actually an Index Delete (or Clustered Index Delete – see below) operator. Front end tools such as SSMS will present it as a JSON Index Update when the IndexKind subproperty in the Object property group is equal to Json.
JSON indexes are implemented as a hidden internal node table, with a clustered index and one or two nonclustered indexes (see here for more details). The JSON Index Delete operator can target any single one of these, or multiple at once. There is nothing in the graphical execution plan, or in the execution plan XML, to distinguish a JSON Index Delete that targets the clustered index from one that targets one of the nonclustered indexes or one that targets a combination.
Visual appearance in execution plans
Depending on the tool being used, an Index Delete operator with one or more delete targets that have their IndexKind property equal to Json is displayed in a graphical execution plan as shown below:
| SSMS and ADS (current versions) |
Legacy SSMS | Plan Explorer | Paste The Plan |
 |
(not supported) | (shown as Index Delete or Clustered Index Delete) | (not supported) |
Algorithm
The JSON Index Delete is not a real operator but merely a visual indicator that an Index Delete or Clustered Index Delete operator is deleting data from one or more JSON indexes. Please check the appropriate pages for a full description of the algorithms of those operators.
Operator properties
The properties below are specific to the JSON Index Delete operator, or have a specific meaning when appearing on it. For all other properties, see Common properties. Properties that are included on the Common properties page but are also included below for their specific meaning for the JSON Index Delete operator are marked with a *.
(Note that most of these properties are exactly the same as for the Index Delete operator; they are repeated here for ease of use).
| Property name | Description |
|---|---|
| DMLRequestSort | When set to true, the delete operation might qualify for minimal logging if additional conditions apply. Details of those additional conditions and the requirements for DMLRequestSort to be true can be found here. |
| Object | The index that the JSON Index Delete operator will delete rows from, using four part naming (database, schema, table, index). Optionally, there may be two or three objects specified. In that case, the first one is always the clustered index on the node table, and the second and third one are the nonclustered secondary index and the optional nonclustered array search index. They, too, are specified using four part naming. The subproperties of the Object property represent the four name parts separately, but also include these additional properties:
|
| Partitioned | This property is present and set to True when the target of the JSON Index Delete is a partitioned index. |
| WithOrderedPrefetch | This property is present (and set to true) when prefetching is requested. This requires the input data to be ordered in the order of the index. Exact details of prefetching in the context of a JSON Index Delete operator are unknown at this time. The assumed effect of prefetching is that the operator issues an asynchronous read request to the storage system, so that read-ahead reading can be used to more efficiently find the rows to be deleted. Rows are still processed in the order in which the operator reads them, but they will already be in the buffer pool as a result of the read ahead prefetching. When this property is not present, no prefetching is used. |
Implicit properties
This table below lists the behavior of the implicit properties for the JSON Index Delete operator
| Property name | Description |
|---|---|
| Batch Mode enabled | The JSON Index Delete operator supports row mode execution only. |
| Blocking | The JSON Index Delete operator is non-blocking. |
| Memory requirement | The JSON Index Delete operator does not have any special memory requirement. |
| Order-preserving | The JSON Index Delete operator is fully order-preserving. |
| Parallelism aware | The JSON Index Delete operator does not support parallelism. It can only be used in a serial plan, or in a serial section of a parallel plan. |
| Segment aware | The JSON Index Delete operator is not segment aware. |
Change log
(Does not include minor changes, such as adding, removing, or changing hyperlinks, correcting typos, and rephrasing for clarity).
July 19, 2026: Added.