How to extract specific text from column

Question

I am using SQL Server 2016 where I am loading JSON Response in one of the nvarchar(max) type column. Now I want to create a derived column or maybe even a view that will find the particular value in that complete JSON Response and display only that in the new derived column.

Example:

**Complete_JSON_Repsone**  --> this is SQL column
{"result":{"banner_image_light":"","country":"USA","parent":"","notes":"","stock_symbol":"","u_op_dev_version":"","u_restriciton":"No","discount":"","sys_id":"7a2c008c1b07ac50a62cea0ce54bcbe8","market_cap":"0","customer":"false"}}

I tried the below query, but it's not giving the expected results it prints out everything after sys_id:

SELECT 
   Substring (
      a.Complete_JSON_Repsone, 
      Charindex( '"sys_id":', Complete_JSON_Repsone) + 1, 
      Len(Complete_JSON_Repsone) 
   ) AS [Sys_Idd] 
FROM <table-name> a

Current output (actual result):

Sys_Idd
sys_id":"7a2c008c1b07ac50a62cea0ce54bcbe8","market_cap":"0","customer":"false"}}      

Expected output:

 Sys_Idd
 7a2c008c1b07ac50a62cea0ce54bcbe8

UPDATE

Sample Input :

Create table dbo.log1
(
  Id varchar(50),
  Complete_JSON_Response nvarchar(max),
  Sys_Id varchar(50)
)


insert into dbo.log1 (Id,Complete_JSON_Response) 
values ('S1','{"result":{"banner_image_light":"","country":"USA","parent":"","notes":"","stock_symbol":"","u_op_dev_version":"","u_restriciton":"No","discount":"","sys_id":"7a2c008c1b07ac50a62cea0ce54bcbe8","market_cap":"0","customer":"false"}}')
,('S2','{"result":{"banner_image_light":"","country":"Aus","parent":"","notes":"","stock_symbol":"","u_op_prod_version":"","u_restriciton":"No","discount":"","sys_id":"5b2c008c1b07ac50a62cea0ce54bcbe8","market_cap":"1","customer":"TRUE"}}')

select * from dbo.log1

Above select query prints, NULL value for Sys_id column as value for that column in not inserted initially. what I want in expected output is that instead of NULL it should populate(derive) only sys_id value from Complete_JSON_Response column to Sys_id column

Expected output:

Id    Sys_Id
S1   7a2c008c1b07ac50a62cea0ce54bcbe8
S2   5b2c008c1b07ac50a62cea0ce54bcbe8

Answer 1

SQL Server 2016 supports JSON, so you may try to use JSON_VALUE():

SELECT JSON_VALUE(Complete_JSON_Response, '$.result.sys_id') AS sys_id
FROM (VALUES
   (N'{
   "result":{
      "banner_image_light":"",
      "country":"USA",
      "parent":"",
      "notes":"",
      "stock_symbol":"",
      "u_op_dev_version":"",
      "u_restriciton":"No",
      "discount":"",
      "sys_id":"7a2c008c1b07ac50a62cea0ce54bcbe8",
      "market_cap":"0",
      "customer":"false"
   }
   }')
) a (Complete_JSON_Response)

As an additional option, you may create a simplified UDF with a recursive search:

CREATE FUNCTION dbo.ParseJson (
    @json nvarchar(max),
    @key nvarchar(max)
)
RETURNS @ResultTable TABLE (
   [value] nvarchar(max)
)
AS
BEGIN
   ;WITH rCTE AS (
      SELECT 
          CONVERT(nvarchar(max), N'$') COLLATE DATABASE_DEFAULT AS [path], 
          CONVERT(nvarchar(max), JSON_QUERY(@json, '$')) COLLATE DATABASE_DEFAULT AS [value]
      UNION ALL
      SELECT 
         CONVERT(nvarchar(max), c.[key]) COLLATE DATABASE_DEFAULT,
         CONVERT(nvarchar(max), c.[value]) COLLATE DATABASE_DEFAULT                                        
      FROM rCTE r
      CROSS APPLY OPENJSON(r.[value]) c
      WHERE ISJSON(r.[value]) = 1
   )
   INSERT INTO @ResultTable ([value])
   SELECT [value]
   FROM rCTE
   WHERE (ISJSON([value]) = 0) AND (path = @key)
   
   RETURN
END

Statement:

DECLARE @json nvarchar(max) = N'
   {
   "result":{
      "banner_image_light":"",
      "country":"USA",
      "parent":"",
      "notes":"",
      "stock_symbol":"",
      "u_op_dev_version":"",
      "u_restriciton":"No",
      "discount":"",
      "sys_id":"7a2c008c1b07ac50a62cea0ce54bcbe8",
      "market_cap":"0",
      "customer":"false"
   },
   "result2":{
      "sys_id":"xxxx008c1b07ac50a62cea0ce54bcbe8"
   }
   }
'

SELECT j.[value] AS sys_id
FROM (VALUES (@json)) a (Complete_JSON_Response)
OUTER APPLY dbo.ParseJson(a.Complete_JSON_Response, 'sys_id') j

Result:

sys_id
--------------------------------
xxxx008c1b07ac50a62cea0ce54bcbe8
7a2c008c1b07ac50a62cea0ce54bcbe8

Answer 2

You can use a function. It may lead some performance issues but works fine.

select
'{"result":{"banner_image_light":"","country":"USA","parent":"","notes":"","stock_symbol":"","u_op_dev_version":"","u_restriciton":"No","discount":"","sys_id":"7a2c008c1b07ac50a62cea0ce54bcbe8","market_cap":"0","customer":"false"}}' json
into tmp_json



CREATE FUNCTION dbo.trialFnc(
    @json nvarchar(max),
    @key nvarchar(255)
)
RETURNS nvarchar(255)
AS 
BEGIN
    declare @txt1 nvarchar(max) = right(@json, len(@json) - (charindex(@key, @json)) + 1 - len(@key) - 3)
    declare @txt2 nvarchar(max) = left(@txt1, charindex('"', @txt1) - 1)

    RETURN @txt2
END;   


select
    dbo.trialFnc(json, 'country') country
,   dbo.trialFnc(json, 'sys_id') sys_id
from tmp_json

It will return you this:

country	sys_id
USA	7a2c008c1b07ac50a62cea0ce54bcbe8