Reputation: 3675
I'm working on a project in which I need the ability to receive and consume (i.e. extract information) from received JSONs. The current version of SQL Server I am using (and will not change for the next couple of years) is 2012, which does not include support for this (as opposed to version 2016).
I do recall seeing a post from someone in which he gently offered a source code for this, but unfortunately can't find it again.
The idea is to have something like:
Having the following JSON:
{
"Obj1": {
"Obj1_S_1": [{
"Obj1_S_1_S_1": "Blabla_1"
}, {
"Obj1_S_1_S_1": "Blabla_2"
}, {
"Obj1_S_1_S_1": "Blabla_3"
}, {
"Obj1_S_1_S_1": "Blabla_4"
}, {
"Obj1_S_1_S_1": "Blabla_5"
}, {
"Obj1_S_1_S_1": "Blabla_6"
}]
},
"Obj2": "This is a simple string",
"Obj3": "456.33"
}
I could use the following invocation:
SET @My_Param = GET_JSON(@Source_JSON, '*.Obj1.Obj1_S_1[3].Obj1_S_1_S_1') ;
and I would get the value 'Blabla_4'
into the variable @My_Param
.
This is the very same syntax used in Oracle and MySQL by the way.
Upvotes: 3
Views: 1834
Reputation: 160
See my response here where I created a function compatible with SQL 2012 that extracts values given the JSON and the column name.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Isaac Adams
-- Create date: 7/12/2018
-- Description: Give the JSON string and the name of the column from which you want the value
-- =============================================
CREATE FUNCTION JSON_VALUE
(
@JSON NVARCHAR(3000),
@column NVARCHAR(3000)
)
RETURNS NVARCHAR(3000)
AS
BEGIN
DECLARE @value NVARCHAR(3000);
DECLARE @trimmedJSON NVARCHAR(3000);
DECLARE @start INT;
DECLARE @length INT;
SET @start = PATINDEX('%' + @column + '":"%',@JSON) + LEN(@column) + 3;
SET @trimmedJSON = SUBSTRING(@JSON, @start, LEN(@JSON));
SET @length = PATINDEX('%", "%', @trimmedJSON);
SET @value = SUBSTRING(@trimmedJSON, 0, @length);
RETURN @value
END
GO
Upvotes: 1
Reputation: 82020
Can be done with some strategic parse/split manipulation
Sample Data
Declare @S varchar(max) ='
{
"Obj1": {
"Obj1_S_1": [{
"Obj1_S_1_S_1": "Blabla_1"
}, {
"Obj1_S_1_S_1": "Blabla_2"
}, {
"Obj1_S_1_S_1": "Blabla_3"
}, {
"Obj1_S_1_S_1": "Blabla_4"
}, {
"Obj1_S_1_S_1": "Blabla_5"
}, {
"Obj1_S_1_S_1": "Blabla_6"
}]
},
"Obj2": "This is a simple string",
"Obj3": "456.33"
}
'
Example
--Clean-up JSON String and add '|||' as a standard delimeter
Select @S = Replace(@S,MapFrm,MapTo)
From (values ('"' ,'')
,(char(13),'|||')
,(char(10),'|||')
,('}' ,'|||')
,('{' ,'|||')
,('[' ,'|||')
,(']' ,'|||')
) b (MapFrm,MapTo)
Option with a Parse/Split UDF
Select Item = left(RetVal,charindex(':',RetVal+':')-1)
,Value = ltrim(right(RetVal,len(RetVal)-charindex(':',RetVal+':')))
From [dbo].[udf-Str-Parse](@S,'|||')
Where Len(IsNull(RetVal,' '))>1
Order By RetSeq
Option without a Parse/Split UDF
Select Item = left(RetVal,charindex(':',RetVal+':')-1)
,Value = ltrim(right(RetVal,len(RetVal)-charindex(':',RetVal+':')))
From (
Select RetSeq = Row_Number() over (Order By (Select null))
,RetVal = LTrim(RTrim(B.i.value('(./text())[1]', 'varchar(max)')))
From (Select x = Cast('<x>' + replace((Select replace(@S,'|||','§§Split§§') as [*] For XML Path('')),'§§Split§§','</x><x>')+'</x>' as xml).query('.')) as A
Cross Apply x.nodes('x') AS B(i)
) A
Where Len(IsNull(RetVal,' '))>1
Order By RetSeq
Both Return
Item Value
Obj1
Obj1_S_1
Obj1_S_1_S_1 Blabla_1
Obj1_S_1_S_1 Blabla_2
Obj1_S_1_S_1 Blabla_3
Obj1_S_1_S_1 Blabla_4
Obj1_S_1_S_1 Blabla_5
Obj1_S_1_S_1 Blabla_6
Obj2 This is a simple string,
Obj3 456.33
The UDF if needed
CREATE FUNCTION [dbo].[udf-Str-Parse] (@String varchar(max),@Delimiter varchar(10))
Returns Table
As
Return (
Select RetSeq = Row_Number() over (Order By (Select null))
,RetVal = LTrim(RTrim(B.i.value('(./text())[1]', 'varchar(max)')))
From (Select x = Cast('<x>' + replace((Select replace(@String,@Delimiter,'§§Split§§') as [*] For XML Path('')),'§§Split§§','</x><x>')+'</x>' as xml).query('.')) as A
Cross Apply x.nodes('x') AS B(i)
);
--Thanks Shnugo for making this XML safe
--Select * from [dbo].[udf-Str-Parse]('Dog,Cat,House,Car',',')
--Select * from [dbo].[udf-Str-Parse]('John Cappelletti was here',' ')
--Select * from [dbo].[udf-Str-Parse]('this,is,<test>,for,< & >',',')
Upvotes: 1