title: JSON_Value error: The argument 2 of the "JSON_VALUE or JSON_QUERY" must be a string literal
body: <p>I'm trying to use JSON_VALUE and SQL Server 2016. I am having to build my SQL query dynamically though.</p>
  <p>JSON:</p>
  <pre><code>{&quot;pageId&quot;:&quot;9&quot;,&quot;moduleId&quot;:&quot;6&quot;,&quot;moduleType&quot;:&quot;Pages&quot;}
  </code></pre>
  <p>Here is my error:</p>
  <p>Argument 2 of the &quot;JSON_VALUE or JSON_QUERY&quot; must be a string literal.</p>
  <pre><code>            var sqlParams = new List&lt;SqlParameter&gt;();
  
              StringBuilder sb = new StringBuilder();
  
              // start the initial select query...
              sb.Append(&quot;SELECT * FROM dbo.FileSystemItems WHERE &quot;);
  
              int counter = 0;
              foreach (var item in metadata)
              {
                  // only add an AND if we are NOT the first record...
                  if (counter != 0)
                  {
                      sb.Append(&quot; AND &quot;);
                  }
  
                  // setup our json path and value items...
                  string pathParam = string.Format(CultureInfo.CurrentCulture, &quot;jsonPathParam{0}&quot;, counter);
                  string valueParam = string.Format(CultureInfo.CurrentCulture, &quot;jsonPathValue{0}&quot;, counter);
  
                  sb.AppendFormat(CultureInfo.CurrentCulture, &quot;JSON_VALUE(FileMetadata, @{0}) = @{1}&quot;, pathParam, valueParam);
  
                  // add in our parameters to assist with sql injection
                  sqlParams.Add(new SqlParameter(pathParam, string.Format(CultureInfo.CurrentCulture, &quot;N'$.{0}'&quot;, item.Key)));
                  sqlParams.Add(new SqlParameter(valueParam, string.Format(CultureInfo.CurrentCulture, &quot;N'{0}'&quot;, item.Value)));
  
                  counter++;
              }
  
              return await BIContext.FileSystemItems
                            .Where(x =&gt; x.ModuleId == moduleId)
                            .FromSql(sb.ToString(), sqlParams.ToArray())
                            .Select(s =&gt; 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);
  </code></pre>
  <p>Here is the generated StringBuilder result as the SQL Query</p>
  <pre><code>SELECT * FROM dbo.FileSystemItems WHERE JSON_VALUE(FileMetadata, @jsonPathParam0) = @jsonPathValue0 AND JSON_VALUE(FileMetadata, @jsonPathParam1) = @jsonPathValue1
  </code></pre>
  <p>I've tried the items from this post:</p>
  <p><a href="https://stackoverflow.com/questions/64114850/json-value-does-not-take-dynamic-json-path">JSON_VALUE does not take dynamic JSON path</a></p>
  <p>but it still gives me the same error.</p>
  <p>What am I doing wrong?</p>
  <p>I need to support 2016 and 2017.</p>
  <p><strong>UPDATE:</strong></p>
  <p>I found this as well: <a href="https://dapper-tutorial.net/knowledge-base/46860751/csharp-dapper-using-json-value-for-sql-server-2016" rel="nofollow noreferrer">https://dapper-tutorial.net/knowledge-base/46860751/csharp-dapper-using-json-value-for-sql-server-2016</a> -&gt;</p>
  <p>This says it might not be possible?</p>
  <p>Then I found this: <a href="https://docs.microsoft.com/en-us/sql/t-sql/functions/json-value-transact-sql?view=sql-server-ver15" rel="nofollow noreferrer">https://docs.microsoft.com/en-us/sql/t-sql/functions/json-value-transact-sql?view=sql-server-ver15</a></p>
  <p>and it says my version is supported:</p>
  <p>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: )</p>
  <p><strong>UPDATE 2:</strong></p>
  <p>After the response below, I found the code that works on SQL server 2016 and SQL server 2017.</p>
  <p>I'm just trying to understand IF/why it protects against SQL Injection because I am not parameterized the 2nd argument of <code>JSON_VALUE()</code></p>
  <pre><code>        public async Task&lt;IList&lt;FileSystemItemJsonDTO&gt;&gt; GetFileSystemItems(int moduleId, IDictionary&lt;string, string&gt; metadata)
          {
              var sqlParams = new List&lt;SqlParameter&gt;();
  
              StringBuilder sb = new StringBuilder();
  
              // start the initial select query...
              sb.Append(&quot;SELECT * FROM dbo.FileSystemItems WHERE &quot;);
  
              int counter = 0;
              foreach (var item in metadata)
              {
                  // only add an AND if we are NOT the first record...
                  if (counter != 0)
                  {
                      sb.Append(&quot; AND &quot;);
                  }
  
                  // setup our json path and value items...
                  string valueParam = string.Format(CultureInfo.CurrentCulture, &quot;jsonPathValue{0}&quot;, counter);
  
                  // 2nd item for JSON_VALUE has to be string literal for SQL server 2016
                  sb.AppendFormat(CultureInfo.CurrentCulture, &quot;JSON_VALUE(FileMetadata, '$.{0}') = @{1}&quot;, item.Key, valueParam);
  
                  // add in our parameters to assist with sql injection
                  sqlParams.Add(new SqlParameter(valueParam, string.Format(CultureInfo.CurrentCulture, &quot;{0}&quot;, item.Value)));
  
                  counter++;
              }
  
              return await BIContext.FileSystemItems
                            .Where(x =&gt; x.ModuleId == moduleId)
                            .FromSql(sb.ToString(), sqlParams.ToArray())
                            .Select(s =&gt; 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);
          }
  </code></pre>