SQL Server 2017中JSON数组过滤查询性能优化咨询

Got it, let's tackle this JSON performance issue in SQL Server 2017. You're right that naive CROSS APPLY OPENJSON can get slow with large datasets, and computed columns can't directly filter for the "Files" key in the array. Here are a few solid alternatives to triggers that should boost your query speed:

1. Rewrite with EXISTS Instead of CROSS APPLY #

The biggest problem with your original query is likely that CROSS APPLY expands every JSON array into rows, even when you only care about a match. Using EXISTS lets the database stop processing a row as soon as it finds a matching "Files" entry, reducing unnecessary computation.

SELECT od.*
FROM OrderData od
WHERE EXISTS (
    -- First, find the "Files" entry in the Data array
    SELECT 1
    FROM OPENJSON(od.DataProperties, '$.Input.Data')
    WITH (
        [Key] NVARCHAR(100) '$.Key',
        Value NVARCHAR(MAX) '$.Value' AS JSON
    ) AS dataItems
    WHERE dataItems.[Key] = 'Files'
    AND EXISTS (
        -- Then check if any filename in the Value array matches your LIKE condition
        SELECT 1
        FROM OPENJSON(dataItems.Value)
        WITH (FileName NVARCHAR(255) '$') AS fileItems
        WHERE fileItems.FileName LIKE '%text%'
    )
)

Why this works: #

• EXISTS short-circuits: as soon as a matching filename is found, the database moves to the next row.
• Avoids creating a large intermediate result set from CROSS APPLY, which saves memory and I/O.

2. Use an Indexed View #

If your query runs frequently and you can tolerate some extra overhead on writes, an indexed view pre-computes the "Files" entries and stores them in an indexed format. This turns your JSON parsing into a simple index lookup.

First, create the view (make sure to use SCHEMABINDING and include COUNT_BIG—required for indexed views):

CREATE VIEW vw_OrderData_Files
WITH SCHEMABINDING
AS
SELECT 
    od.OrderId, -- Replace with your table's primary key
    fileItems.FileName,
    COUNT_BIG(*) AS RowCount -- Mandatory for indexed views
FROM dbo.OrderData od
CROSS APPLY OPENJSON(od.DataProperties, '$.Input.Data')
WITH (
    [Key] NVARCHAR(100) '$.Key',
    Value NVARCHAR(MAX) '$.Value' AS JSON
) AS dataItems
CROSS APPLY OPENJSON(dataItems.Value)
WITH (FileName NVARCHAR(255) '$') AS fileItems
WHERE dataItems.[Key] = 'Files'
GROUP BY od.OrderId, fileItems.FileName;

Then create a unique clustered index on the view:

CREATE UNIQUE CLUSTERED INDEX IX_vw_OrderData_Files 
ON vw_OrderData_Files (OrderId, FileName);

Now query using the view to get fast, indexed access:

SELECT od.*
FROM dbo.OrderData od
JOIN dbo.vw_OrderData_Files vw 
    ON od.OrderId = vw.OrderId
WHERE vw.FileName LIKE '%text%';

Why this works: #

• The indexed view stores pre-split "Files" entries, so your query doesn't need to parse JSON on the fly.
• SQL Server automatically maintains the view when you insert/update/delete rows in OrderData—no custom triggers needed.

3. Full-Text Index on the JSON Column #

If your LIKE pattern is a simple substring match, a full-text index can quickly narrow down rows that contain the target text, then you can filter those rows for the "Files" key.

First, set up the full-text catalog and index:

-- Create a full-text catalog if you don't have one
CREATE FULLTEXT CATALOG ft_OrderData_Catalog AS DEFAULT;

-- Create full-text index on the JSON column
CREATE FULLTEXT INDEX ON OrderData(DataProperties)
KEY INDEX PK_OrderData -- Replace with your table's primary key index name
WITH STOPLIST = OFF; -- Disable stopwords to match all substrings

Then query by combining full-text search with JSON validation:

SELECT od.*
FROM OrderData od
-- First, use full-text to find rows containing "text"
WHERE CONTAINS(od.DataProperties, '"*text*"')
-- Then verify the text is in a "Files" entry
AND EXISTS (
    SELECT 1
    FROM OPENJSON(od.DataProperties, '$.Input.Data')
    WITH (
        [Key] NVARCHAR(100) '$.Key',
        Value NVARCHAR(MAX) '$.Value' AS JSON
    ) AS dataItems
    WHERE dataItems.[Key] = 'Files'
    AND EXISTS (
        SELECT 1
        FROM OPENJSON(dataItems.Value)
        WHERE value LIKE '%text%'
    )
)

Why this works: #

• Full-text indexes are optimized for substring searches and can filter out most non-matching rows quickly.
• You only parse JSON for the rows that already contain your target text, drastically reducing the number of JSON operations.

Key Notes: #

• Make sure your JSON paths ($.Input.Data, etc.) are correct for your actual data structure.
• For indexed views, ensure your primary key is included in the view to join back to the original table.
• Full-text indexes work best for simple substring matches; if you need complex pattern matching, stick with the EXISTS or indexed view approach.

内容的提问来源于stack exchange，提问作者Lars