Author: Onur Kulabas
Date: 24 Oct, 2020

SQL Server JSON_VALUE Function

JSON_VALUE function can be used to extract scalar value from a given JSON string on SQL Server 2016 and later versions. We will give examples of dynamic usage of the function.

Syntax of function is : JSON_VALUE ( expression , path )

Sample usage of the function is as below;

DECLARE @jsonURL as varchar(50), @MYJSON as varchar(500)
set @MYJSON = '[{"Value1":"2142.08","id":2188560},{"Value1":"116.2","Value2":"1914.16","id":2188561}'
set @jsonURL = '$[0]."Value1"'

print JSON_VALUE(@MYJSON,''+ @jsonURL +'')

Output will be 2142.08. Index value is 0, so Value1 will be extracted as 2142.08 from the first set -> {"Value1":"2142.08","id":2188560}

We usually need to write the @jsonURL variable dynamically. The below sample is as;

DECLARE @jsonURL as varchar(50), @MYJSON as varchar(500), @jsonIndex as varchar(50), @i as int , @FieldName as varchar(50)

set @MYJSON = '[{"Value1":"2142.08","id":2188560},{"Value1":"116.2","Value2":"1914.16","id":2188561}'
SET @i = 0
SET @FieldName = 'Value1'
set @jsonIndex = '[' + convert(varchar,@i) + ']'
set @jsonURL = '$' + @jsonIndex + '."' + @FieldName + '"'

print JSON_VALUE(@MYJSON,''+ @jsonURL +'')

Pinea Blog

Follow us for the latest technology news!

SQL JSON_VALUE
06Dec
Query Store
 

Query Store is a new feature introduced in SQL Server 2016. It can be considered as a flight recorder or a black box...

Continue Reading ...
SQL JSON_VALUE
19Nov
Creating Database Users
without Logins

Contained databases include all database settings and metadata to define the database and has no ...

Continue Reading ...
PM2
30Oct
PM2

PM2 is an advanced process manager for the JavaScript runtime Node.js. You can manage your node packages on...

Continue Reading ...
SQL JSON_VALUE
24Oct
SQL Server
JSON_VALUE Function

JSON_VALUE function can be used to extract scalar value from a given JSON string on SQL Server...

Continue Reading ...
CORS
21Oct
What is
CORS?

Cross-origin resource sharing (CORS) is the mechanism that manages the cross-domain requests, ...

Continue Reading ...