1

I have a table that with a column Info VARCHAR(MAX) constrained to always be valid json. What is the best way to query a key with JSON_QUERY/JSON_VALUE if I don't know ahead of time if the value associated with the key is a scalar or not? Currently I am returning where either has a value as shown below.

SELECT ID, JSON_VALUE(Info, '$.Key') as json_val, JSON_QUERY(Info,  '$.Key') as json_query
FROM TABLE
WHERE (JSON_VALUE(Info, '$.Key') IS NOT NULL OR JSON_QUERY(Info, '$.Key') IS NOT NULL)

and relying on the fact that the results are mutually exclusive.

The problem is the JSON_QUERY in the WHERE clause prevents any indexes on the virual column vKey AS JSON_VALUE(Info, '$.Key') from being used.

will smith
  • 13
  • 3
  • One possibility might be to define the computed column as `vKey AS ISNULL(JSON_VALUE(Info, '$.Key'), JSON_QUERY(Info, '$.Key') )` and then change the query to `SELECT ID, CASE WHEN ISJSON(vKey) = 0 THEN vKey END as json_val, CASE WHEN ISJSON(vKey) = 1 THEN vKey END as json_query FROM "TABLE" WHERE vKey IS NOT NULL` – Martin Smith Aug 08 '22 at 19:43
  • a scalar function that returns (select o.value from openjson(@info) as o where o.[key] = 'key') ..a computed column as dbo.f(info) and an index on the computed column – lptr Aug 08 '22 at 19:53
  • @Martin Smith Thanks for your suggestion. Virtual columns backed by `JSON_QUERY` can't be used in an index. using `ISNULL` how you show does seem to allow it to be created, but it isn't being used in the query plan. – will smith Aug 08 '22 at 20:08
  • you probably have to `INCLUDE (ID)` to make the index covering. This would only be suitable if the potential object returned by `JSON_QUERY(Info, '$.Key')` is always going to be short enough to fit into an index key (<= 1700 bytes) – Martin Smith Aug 08 '22 at 20:16
  • Execution plan using this index https://i.stack.imgur.com/QyAEh.png – Martin Smith Aug 08 '22 at 20:16

1 Answers1

2

As suggested by @MartinSmith, you can add a computed column like this

ALTER TABLE YourTable
  ADD YourColumn AS (ISNULL(JSON_VALUE(json, '$.key'), JSON_QUERY(json, '$.key')));

You can then index it, and the index will be used in queries automatically

CREATE INDEX IndexName ON YourTable (YourColumn) INCLUDE (OtherColumns)

Note that the index will normally be used even if you use the original expression rather than the new column name.

db<>fiddle

Charlieface
  • 52,284
  • 6
  • 19
  • 43