基于JSON数组的FileMetadata字段SQL查询失败原因咨询

The issue with your original query is straightforward: your FileMetadata column now stores a JSON array of key-value pairs, but your SQL uses path syntax designed for a single JSON object.

Let's break down the problem #

Your original query:

SELECT * FROM FileSystemItems WHERE JSON_VALUE(FileMetadata, '$.Key') = 'scheduleId' and JSON_VALUE(FileMetadata, '$.Value') = '82'

This works when FileMetadata looks like a single object (e.g., {"Key":"scheduleId","Value":"82"}), because $.Key targets the top-level Key property of that object.

But now FileMetadata is an array (like [{"Key":"schedulerItemType","Value":"schedule"},{"Key":"scheduleId","Value":"82"},...]). The top-level structure is an array, not an object—there is no $.Key property at the root. So JSON_VALUE returns NULL for these paths, and your condition never matches any rows.

Fix 1: Use OPENJSON to parse the array #

The most flexible approach is to use OPENJSON to unpack the array into a rowset, then check if any of those rows match your key-value pair:

SELECT fs.* 
FROM FileSystemItems fs
WHERE EXISTS (
    SELECT 1
    FROM OPENJSON(fs.FileMetadata)
    WITH (
        Key NVARCHAR(255) '$.Key',
        Value NVARCHAR(255) '$.Value'
    ) AS json_rows
    WHERE json_rows.Key = 'scheduleId' AND json_rows.Value = '82'
)

This query:

1. Uses OPENJSON to turn each element in the JSON array into a row with Key and Value columns
2. Checks if any of those rows match your target key-value pair
3. Returns the full FileSystemItems row if a match exists

Fix 2: Use JSON_CONTAINS (simpler for exact object matches) #

If you just need to check if the array contains the exact key-value object, you can use JSON_CONTAINS:

SELECT * 
FROM FileSystemItems
WHERE JSON_CONTAINS(FileMetadata, '{"Key":"scheduleId", "Value":"82"}')

This is shorter, but less flexible if you need to do more complex filtering on the JSON data (like partial matches or combining multiple conditions).

Key Takeaway #

When working with JSON arrays in SQL Server, you can't use top-level object paths like $.Key—you need to either unpack the array with OPENJSON or use array-aware functions like JSON_CONTAINS.

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