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

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://docs.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);          }  

https://stackoverflow.com/questions/66500764/json-value-error-the-argument-2-of-the-json-value-or-json-query-must-be-a-str March 06, 2021 at 07:14AM