How to parse json data in SQL Server 2012?

Question

I am using SQL Server 2012.I have been assigned a task where one of my column (JsonText) of table Sample contains json data. I want to pass parse that data and insert into columns of another table (Test). I searched on net 'openjson' is supported in SQL Server 2016. How to do in SQL Server 2012?

Table1 : Sample

Id JsonText Active

JsonText

webaddress?{'data':'{"PId": "XXXX","Status": "YES","Name":"XXX","Address":"XXXX","MobileNumber":"xxx"}'}

I am intrested only 'PID,Address,MobileNumber' columns not all.

Table Test structure like this

Id, PID, Address, MobileNumber

Check [this](https://msdn.microsoft.com/en-us/library/dn921897.aspx). Sorry, this is for 2016 and you need for 2012. Ignore this comment. — FDavidov, Dec 12 '16 at 06:30
Found [this](https://www.simple-talk.com/sql/t-sql-programming/consuming-json-strings-in-sql-server/) that may solve your problem (didn't check it, but it looks a reasonable solution). — FDavidov, Dec 12 '16 at 08:08
Prior to 2016, you'd need a specialist script like [this one](http://mtkcode.blogspot.co.uk/2014/08/parse-json-string-by-sql-script.html), or to use CLRs I believe. Have a look at these previous SO questions and their answers: [Parse JSON string in sql](http://stackoverflow.com/questions/12235504/parse-json-string-in-sql) and [Parse JSON in TSQL](http://stackoverflow.com/questions/2867501/parse-json-in-tsql). — 3N1GM4, Dec 12 '16 at 08:59
Possible duplicate of [Parse JSON in TSQL](http://stackoverflow.com/questions/2867501/parse-json-in-tsql) — Bridge, Apr 07 '17 at 11:44

score 2 · Answer 1 · answered Apr 30 '20 at 13:46

Isaac your code is not working with not quoted values e.g. {"isAuthorized":"false","customerID":null}. I fixed this and your function should look like this.

ALTER FUNCTION [dbo].[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 @end INT;

set @start = PATINDEX('%' + @column + '":%',@JSON) + LEN(@column) + 2;
SET @trimmedJSON = SUBSTRING(@JSON, @start, LEN(@JSON));
Set @end = CHARINDEX(',',@trimmedJSON);
SET @value = REPLACE(SUBSTRING(@trimmedJSON, 0, @end),'"','');

RETURN @value
END

score 0 · Answer 2 · answered Jul 13 '18 at 01:17

I created a function compatible with SQL 2012 to take care of this

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

I am also using your same function,but I am not able to get the result.SELECT dbo.JSON_value('{street:"street1",street2:"street232423"}','street').It needs to give value stree1,but its gives empty value — Techy, May 17 '20 at 00:35

score -2 · Answer 3 · edited Apr 17 '17 at 17:34

-2

You can use JSON_VALUE(ColumnName,'$.Path') for pairs Json in TSQL, for example:

select JSON_VALUE(webaddress,'$.data.PID') as 'PID',
       JSON_VALUE(webaddress,'$.data.Status') as 'Status',
       JSON_VALUE(webaddress,'$.data.Name') as 'Name'
from test

edited Apr 17 '17 at 17:34

CDspace

2,639
18
30
36

answered Apr 17 '17 at 16:12

hs.jalilian

103
1
2

4

You can use it only with MSSQL 2016+ (https://learn.microsoft.com/en-us/sql/t-sql/functions/json-value-transact-sql) – Ihor Shubin Jul 25 '17 at 10:36

score -2 · Answer 4 · edited Mar 04 '21 at 13:10

-2

>>> at JSON_VALUE function, at PATINDEX('%", "%', @trimmedJSON);

remove space from '%", "%'

if your JSON value is like

'{"street":"street1","street2":"street232423"}'

edited Mar 04 '21 at 13:10

bguiz

27,371
47
154
243

answered Mar 04 '21 at 09:47

ShahrzadJv

1

How to parse json data in SQL Server 2012?

4 Answers4

Linked