tsql - C# Dapper using JSON_VALUE for SQL Server 2016

Question

I want to query data from my table using JSON_VALUE:

var str = "123";
var value = "Name"
using(var conn = GetMyConnection())
{
   var result = conn.QueryFirstOrDefault<string>(
      @"SELECT [Id] FROM [dbo].[MyTable]
         WHERE JSON_VALUE([JsonColumn], @MyQuery) = @Str",
      new
      {
         MyQuery = $"$.{value}",
         Str = str
      }
   );
}

I try this in SQL Server, it is working:

SELECT [Id] FROM [dbo].[MyTable]
WHERE JSON_VALUE([JsonColumn], '$.Name') = '123'

How should I adjust my code?

Answer 1

I try this in SQL Server, it working

First of all you miss one important thing variable vs literal.

It won't work on SQL Server 2016 when used in SSMS:

CREATE TABLE MyTAble(ID INT IDENTITY(1,1), JsonColumn NVARCHAR(MAX));

INSERT INTO MyTable( JsonColumn)
VALUES('{"Name":123}');


-- it will work
SELECT [Id] FROM [dbo].[MyTable]
WHERE JSON_VALUE([JsonColumn], '$.Name') = '123';

-- let''s try your example
DECLARE @Path NVARCHAR(MAX) = '$.Name';
SELECT [Id] FROM [dbo].[MyTable]
WHERE JSON_VALUE([JsonColumn], @Path) = '123';

DBFiddle Demo

You will get:

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

---

Second from SQL Server 2017+ you could pass path as variable. From JSON_VALUE:

path

A JSON path that specifies the property to extract. For more info, see JSON Path Expressions (SQL Server).

In SQL Server 2017 and in Azure SQL Database, you can provide a variable as the value of path.

DbFiddle Demo 2017

---

And finally to get it work on SQL Server 2016 you may build your query string using concatenation (instead of parameter binding).

Warning! This could lead to serious security problem and SQL Injection attacks.