Edit

sys.json_index_paths (Transact-SQL)

Applies to: SQL Server 2025 (17.x) Azure SQL Database Azure SQL Managed Instance SQL database in Microsoft Fabric

Contains the SQL/JSON paths for a JSON index. If the CREATE JSON INDEX statement doesn't define a sql_json_path, this catalog view contains one row with a root SQL/JSON path S for that index.

Column name Data type Description
object_id int ID of table with JSON column.
index_id int ID of JSON index.
path varchar(8000) SQL/JSON path. Collation of the path column is fixed to Latin1_General_100_BIN2_UTF8.

Permissions

The visibility of the metadata in catalog views is limited to securables that a user either owns, or on which the user was granted some permission. For more information, see Metadata visibility configuration.

Examples

A. JSON index with no paths

The following example returns JSON indexes for the table dbo.Customers. The JSON index is created without specifying any SQL/JSON path.

DROP TABLE IF EXISTS dbo.Customers;

CREATE TABLE dbo.Customers
(
    customer_id INT IDENTITY PRIMARY KEY,
    customer_info JSON NOT NULL
);

CREATE JSON INDEX CustomersJsonIndex
    ON dbo.Customers (customer_info);

INSERT INTO dbo.Customers (customer_info)
VALUES ('{"name":"customer1", "email": "customer1@example.com", "phone":["123-456-7890", "234-567-8901"]}');

SELECT object_id,
       index_id,
       path
FROM sys.json_index_paths
WHERE object_id = OBJECT_ID('dbo.Customers');

B. JSON index for a specific path

The following example returns JSON indexes for the table dbo.Customers. The JSON index is created for a specific SQL/JSON path $.phone.

DROP TABLE IF EXISTS dbo.Customers;

CREATE TABLE dbo.Customers
(
    customer_id INT IDENTITY PRIMARY KEY,
    customer_info JSON NOT NULL
);

CREATE JSON INDEX CustomersJsonIndex
    ON dbo.Customers (customer_info)
    FOR ('$.phone') WITH (OPTIMIZE_FOR_ARRAY_SEARCH = ON);

INSERT INTO dbo.Customers (customer_info)
VALUES ('{"name":"customer1", "email": "customer1@example.com", "phone":["123-456-7890", "234-567-8901"]}');
SELECT object_id,
       index_id,
       path
FROM sys.json_index_paths
WHERE object_id = OBJECT_ID('dbo.Customers');

C. JSON index for multiple paths

The following example returns JSON indexes for the table dbo.Customers. The JSON index is created for multiple SQL/JSON paths $.name and $.email.

DROP TABLE IF EXISTS dbo.Customers;

CREATE TABLE dbo.Customers
(
    customer_id INT IDENTITY PRIMARY KEY,
    customer_info JSON NOT NULL
);

CREATE JSON INDEX CustomersJsonIndex
    ON dbo.Customers (customer_info)
    FOR ('$.name', '$.email');

INSERT INTO dbo.Customers (customer_info)
VALUES ('{"name":"customer1", "email": "customer1@example.com", "phone":["123-456-7890", "234-567-8901"]}');

SELECT object_id,
       index_id,
       path
FROM sys.json_index_paths
WHERE object_id = OBJECT_ID('dbo.Customers');

Related content

  • Last updated on