6

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?

Lukasz Szozda
  • 162,964
  • 23
  • 234
  • 275
Max
  • 4,439
  • 2
  • 18
  • 32
  • I suggest you create a Stored Procedure and Give away your json string as a parameter and handle it there. – Masoud Andalibi Oct 21 '17 at 10:40
  • I tried this code on my local environment. And it works correctly. What is the problem with the code? – Serkan Arslan Oct 21 '17 at 20:08
  • @sarslan It won't work because OP pass it as literal, while in C# he tries to pass it as variable. Only way in SQL Server is to use dynamic SQL or build query string in C# code. – Lukasz Szozda Oct 22 '17 at 15:19
  • @lad2025 Actualy, I tried it with Azure Sql and it worked. You will be right, because I did not try it with sql 2016. – Serkan Arslan Oct 22 '17 at 15:48
  • @sarslan As in my answer `In SQL Server 2017 and in Azure SQL Database, you can provide a variable as the value of path.` Azure is always ahead that on-premise :) – Lukasz Szozda Oct 22 '17 at 15:49

1 Answers1

9

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.

Lukasz Szozda
  • 162,964
  • 23
  • 234
  • 275
  • 1
    Just a note if somebody's having these issues: I also got this error on SQL Server 2016 even though I was actually using a string literal. The issue was that I was using forced parameterization. So the solution for me was to temporarily switch to simple parametrization, run the query, and then switch to forced parametrization again. – Tom Pažourek Jul 18 '18 at 08:38
  • @TomPažourek Great observation. – Lukasz Szozda Jul 18 '18 at 19:09