SQL Server 2025: JSON Indexes
Shred the documents once — then seek like any other index
SELECT
*
FROM
dbo.Users
WHERE JSON_VALUE
(UserAttributes,
'$.Location.City') = 'Helsinki';
CREATE JSON INDEX
JX_Users
ON
dbo.Users (UserAttributes)
FOR
('$.Location.Country',
'$.Location.Province','$.Location.City');
SELECT
*
FROM
dbo.Users
WHERE JSON_VALUE
(UserAttributes,
'$.Location.Country') = 'Finland';
dbo.Users · clustered on Id
Id 8,114
{"Location": {"Country": "Finland",
"Province": "Uusimaa", "City": "Espoo"},
"Theme": "dark"
}
parse
Id 26,837
{"Location": {"Country": "Finland",
"Province": "Uusimaa", "City": "Helsinki"},
"Theme": "dark"
}
parse
Id 77,351
{"Location": {"Country": "Canada",
"Province": "Ontario", "City": "Toronto"},
"Theme": "dark"
}
parse
Id 590,442
{"Location": {"Country": "Finland",
"Province": "Uusimaa", "City": "Helsinki"},
"Theme": "light"
}
parse
JSON index · sorted by (path, value)
$.Location.City
Espoo · Id 8,114
Helsinki · Id 26,837
Helsinki · Id 590,442
Toronto · Id 77,351
$.Location.Country
Canada · Id 77,351
Finland · Id 8,114
Finland · Id 26,837
Finland · Id 590,442
$.Location.Province
Ontario · Id 77,351
Uusimaa · Id 8,114
Uusimaa · Id 26,837
Uusimaa · Id 590,442
"Theme" wasn’t in the FOR list — never shredded, never indexed
$.Location.City = Helsinki
$.Location.Country = Finland
$.Location.Province = Uusimaa
before: every page + every parse · after: one seek + 2 lookups
the shredder runs on every INSERT & UPDATE