Search code examples
jsonsql-serversql-server-json

How to query a key in a SQL Server Json column if it could be a scalar value or an object?

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.

Solution

  • 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