JSON_Value error: The argument 2 of the "JSON_VALUE or JSON_QUERY" must be a string literal

Question

I'm trying to use JSON_VALUE and SQL Server 2016. I am having to build my SQL query dynamically though.

JSON:

{"pageId":"9","moduleId":"6","moduleType":"Pages"}

Here is my error:

Argument 2 of the "JSON_VALUE or JSON_QUERY" must be a string literal.

            var sqlParams = new List<SqlParameter>();

            StringBuilder sb = new StringBuilder();

            // start the initial select query...
            sb.Append("SELECT * FROM dbo.FileSystemItems WHERE ");

            int counter = 0;
            foreach (var item in metadata)
            {
                // only add an AND if we are NOT the first record...
                if (counter != 0)
                {
                    sb.Append(" AND ");
                }

                // setup our json path and value items...
                string pathParam = string.Format(CultureInfo.CurrentCulture, "jsonPathParam{0}", counter);
                string valueParam = string.Format(CultureInfo.CurrentCulture, "jsonPathValue{0}", counter);

                sb.AppendFormat(CultureInfo.CurrentCulture, "JSON_VALUE(FileMetadata, @{0}) = @{1}", pathParam, valueParam);

                // add in our parameters to assist with sql injection
                sqlParams.Add(new SqlParameter(pathParam, string.Format(CultureInfo.CurrentCulture, "N'$.{0}'", item.Key)));
                sqlParams.Add(new SqlParameter(valueParam, string.Format(CultureInfo.CurrentCulture, "N'{0}'", item.Value)));

                counter++;
            }

            return await BIContext.FileSystemItems
                          .Where(x => x.ModuleId == moduleId)
                          .FromSql(sb.ToString(), sqlParams.ToArray())
                          .Select(s => new FileSystemItemJsonDTO
                          {
                              FileId = s.FileId,
                              FileName = s.FileName,
                              FileType = s.FileType,
                              LastWriteTime = s.LastWriteTime,
                              FileSystemItemDataId = s.FileSystemItemDataId,
                              ModuleId = moduleId,
                              FileMetadata = s.FileMetadata,
                              FileSize = s.FileSize
                          })
                          .ToListAsync().ConfigureAwait(false);

Here is the generated StringBuilder result as the SQL Query

SELECT * FROM dbo.FileSystemItems WHERE JSON_VALUE(FileMetadata, @jsonPathParam0) = @jsonPathValue0 AND JSON_VALUE(FileMetadata, @jsonPathParam1) = @jsonPathValue1

I've tried the items from this post:

JSON_VALUE does not take dynamic JSON path

but it still gives me the same error.

What am I doing wrong?

I need to support 2016 and 2017.

UPDATE:

I found this as well: https://dapper-tutorial.net/knowledge-base/46860751/csharp-dapper-using-json-value-for-sql-server-2016 ->

This says it might not be possible?

Then I found this: https://learn.microsoft.com/en-us/sql/t-sql/functions/json-value-transact-sql?view=sql-server-ver15

and it says my version is supported:

Microsoft SQL Server 2016 (SP2-CU15-GDR) (KB4583461) - 13.0.5865.1 (X64) Oct 31 2020 02:43:57 Copyright (c) Microsoft Corporation Developer Edition (64-bit) on Windows 10 Enterprise 10.0 (Build 19041: )

UPDATE 2:

After the response below, I found the code that works on SQL server 2016 and SQL server 2017.

I'm just trying to understand IF/why it protects against SQL Injection because I am not parameterized the 2nd argument of JSON_VALUE()

        public async Task<IList<FileSystemItemJsonDTO>> GetFileSystemItems(int moduleId, IDictionary<string, string> metadata)
        {
            var sqlParams = new List<SqlParameter>();

            StringBuilder sb = new StringBuilder();

            // start the initial select query...
            sb.Append("SELECT * FROM dbo.FileSystemItems WHERE ");

            int counter = 0;
            foreach (var item in metadata)
            {
                // only add an AND if we are NOT the first record...
                if (counter != 0)
                {
                    sb.Append(" AND ");
                }

                // setup our json path and value items...
                string valueParam = string.Format(CultureInfo.CurrentCulture, "jsonPathValue{0}", counter);

                // 2nd item for JSON_VALUE has to be string literal for SQL server 2016
                sb.AppendFormat(CultureInfo.CurrentCulture, "JSON_VALUE(FileMetadata, '$.{0}') = @{1}", item.Key, valueParam);

                // add in our parameters to assist with sql injection
                sqlParams.Add(new SqlParameter(valueParam, string.Format(CultureInfo.CurrentCulture, "{0}", item.Value)));

                counter++;
            }

            return await BIContext.FileSystemItems
                          .Where(x => x.ModuleId == moduleId)
                          .FromSql(sb.ToString(), sqlParams.ToArray())
                          .Select(s => new FileSystemItemJsonDTO
                          {
                              FileId = s.FileId,
                              FileName = s.FileName,
                              FileType = s.FileType,
                              LastWriteTime = s.LastWriteTime,
                              FileSystemItemDataId = s.FileSystemItemDataId,
                              ModuleId = moduleId,
                              FileMetadata = s.FileMetadata,
                              FileSize = s.FileSize
                          })
                          .ToListAsync().ConfigureAwait(false);
        }

Answer 1

As mentioned by @user2864740, we can rewrite this using a Table-Valued Parameter and OPENJSON.

First create a table type, I suggest you think carefully about the exact column types and lengths:

CREATE TYPE Metadata TABLE (jsonKey nvarchar(100) PRIMARY KEY, jsonValue nvarchar(1000) NOT NULL);

And the query uses relational division:

SELECT *    -- Preferably don't use select *, specify necessary columns instead
FROM dbo.FileSystemItems
WHERE moduleId = @moduleId
AND NOT EXISTS (
    SELECT m.jsonKey, m.jsonValue
    FROM @metadata m
    EXCEPT
    SELECT j.[key], j.[value]
    FROM OPENJSON(FileMetadata) j
);

This basically says: there must not be any metadata requirements which DON'T have a matching key/value pair.

Another, sometimes more efficient version, of the same thing:

AND EXISTS (SELECT 1
    FROM @metadata m
    LEFT JOIN OPENJSON(FileMetadata) j
        ON j.[key] = m.jsonKey AND j.[value] = m.jsonValue
    HAVING COUNT(j.[Key]) = COUNT(*)
);

This says: if we add up all metadatas which have matching key/values, they must be the same as the number of all metadatas.

Were you to want an OR semantic, a simple join would suffice:

AND EXISTS (SELECT 1
    FROM @metadata m
    JOIN OPENJSON(FileMetadata) j
        ON j.[key] = m.jsonKey AND j.[value] = m.jsonValue
);

---

Now lets' query this in C#. Add in the query above in the correct place.

I tend to make the TVP parameter construction into an extension method, I will leave that to you.

public async Task<IList<FileSystemItemJsonDTO>> GetFileSystemItems(int moduleId, IDictionary<string, string> metadata)
{
    var table = new DataTable();
    table.Columns.Add("jsonKey", typeof(string));
    table.Columns.Add("jsonValue", typeof(string));
    foreach (var item in metadata)
        table.Add(item.Key, item.Value);

    var params = new[] {
        new SqlParameter("@metadata", SqlDbType.Structured)
        {
            Direction = ParameterDirection.Input,
            TypeName = "dbo.Metadata",
            Value = table
        },
        new SqlParameter("@moduleId", SqlDbType.Int){Value = moduleId}
    };
    const string query = @"
SELECT ...
";
    return await BIContext.FileSystemItems
                          .FromSql(MyQuery, params)
                          .Select(s => new FileSystemItemJsonDTO
                          {
                              FileId = s.FileId,
                              FileName = s.FileName,
                              FileType = s.FileType,
                              LastWriteTime = s.LastWriteTime,
                              FileSystemItemDataId = s.FileSystemItemDataId,
                              ModuleId = moduleId,
                              FileMetadata = s.FileMetadata,
                              FileSize = s.FileSize
                          })
                          .ToListAsync().ConfigureAwait(false);
}