JSON Index Insert
Introduction
The JSON Index Insert 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 Insert (or Clustered Index Insert – see below) operator. Front end tools such as SSMS will present it as a JSON Index Insert 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 Insert 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 Insert 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 Insert operator with one or more insert targets that have their IndexKind property equal to Json is displayed in a graphical execution plan as shown below:
|
SSMS and ADS |
Legacy SSMS |
Plan Explorer |
Paste The Plan |
 |
(not supported) |
(shown as Index Insert or Clustered Index Insert) |
(not supported) |
Algorithm
The JSON Index Insert is not a real operator but merely a visual indicator that an Index Insert or Clustered Index Insert operator is inserting data in 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 Insert 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 Insert operator are marked with a *.
(Note that most of these properties are exactly the same as for the Index Insert operator; they are repeated here for ease of use).
| Property name | Description |
|---|---|
| Actual Number of Rows * | This is always 0 if the JSON Index Insert operator is used to create a new index, because it does not return any rows to its parent in that case. |
| DMLRequestSort | When set to true, the insert 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. |
| Estimated Number of Rows * | This is always equal to the Estimated Number of Rows from the child operator, even when the JSON Index Insert operator is used to create a new index and hence will not return any rows to its parent. |
| Object | The index that the JSON Index Insert operator will insert rows to, 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 Insert is a partitioned index. |
| Predicate | Maps columns from the input stream to the columns in the JSON index specified in the Object property, or sets these columns to hardcoded values or variables from the query text. |
| 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 Insert operator.
| Property name | Description |
|---|---|
| Batch Mode enabled | The JSON Index Insert operator supports row mode execution only. |
| Blocking | The JSON Index Insert operator is non-blocking. |
| Memory requirement | The JSON Index Insert operator does not have any special memory requirement. |
| Order-preserving | The JSON Index Insert operator is fully order-preserving when no prefetching is used. When the WithUnorderedPrefetch property is present and true, then the JSON Index Insert operator is not order-preserving. |
| Parallelism aware | The JSON Index Insert 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 Insert 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.