Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
1.0k views
in Technique[技术] by (71.8m points)

tsql - C# Dapper using JSON_VALUE for SQL Server 2016

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?

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Answer

0 votes
by (71.8m points)

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.


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...