CODEWITHSUNDEEP
sql-serverjsonstringt-sql
samthebrand
samthebrand

Reputation: 3090

Parsing JSON with SQL: How to extract a record within a JSON object?

I'm looking at about 13,000 rows in a SQL Server table, and trying to parse out certain values within one column that is stored as json.

The json column values look something like this:

..."http://www.companyurl.com","FoundedYear":"2007","Status":"Private","CompanySize":"51-200","TagLine":"We build software we believe in","Origi...

I'd like to extract the value for "CompanySize", but not all rows include this attribute. Other complicating factors:

The one rule I know for certain: the CompanySize value is always a string of unknown length that follows the varchar string "CompanySize":" and terminates before the next "," string.

Ideally we would have upgraded fully to SQL Server 2016 so I'd be able to take advantage of SQL Server's JSON support, but that's not the case.

Upvotes: 3

Views: 3571

Answers (2)

G-Wiz
G-Wiz

Reputation: 7426

Building on @srutzky 's answer, the following solution avoids creating a UDF (although you didn't say that was a constraint, it might be useful for some).

select
    c.Id,
    substring(i2.jsontail, 0, i3.[length]) CompanySize
from
    Companies c cross apply
    ( select charindex('CompanySize":"', c.json) start ) i1 cross apply
    ( select substring(c.json, start + len('CompanySize":"'), len(c.json) - start ) jsontail ) i2 cross apply
    ( select charindex('"', i2.jsontail) [length] ) i3
where
    i1.[start] != 0

Upvotes: 1

Solomon Rutzky
Solomon Rutzky

Reputation: 48806

You can do this with CHARINDEX since you can pass it a start position, which will allow you to get the closing ". You probably shouldn't look for "," since if CompanySize is the final property, it won't have the ," at the end of that fragment. Doing this as an Inline Table-Valued Function (iTVF) will be pretty efficient (especially since 13k rows is almost nothing), you just need to use it with either CROSS APPLY or OUTER APPLY:

USE [tempdb];
GO

CREATE FUNCTION dbo.GetCompanySize(@JSON NVARCHAR(MAX))
RETURNS TABLE
AS RETURN

WITH SearchStart AS
(
  SELECT '"CompanySize":"' AS [Fragment]
), Search AS
(
  SELECT CHARINDEX(ss.Fragment, @JSON) AS [Start],
         LEN(ss.Fragment) AS [FragmentLength]
  FROM   SearchStart ss
)
SELECT CASE Search.Start
         WHEN 0 THEN NULL
         ELSE SUBSTRING(@JSON,
                        (Search.Start + Search.FragmentLength),
                        CHARINDEX('"',
                                  @JSON,
                                  Search.Start + Search.FragmentLength
                                 ) - (Search.Start + Search.FragmentLength)
                       )
       END AS [CompanySize]
FROM Search;
GO

Set up the test:

CREATE TABLE #tmp (JSON NVARCHAR(MAX));

INSERT INTO #tmp (JSON) VALUES
('"http://www.companyurl.com","FoundedYear":"2007","Status":"Private","CompanySize":"51-200","TagLine":"We build software we believe in","Origi..');
INSERT INTO #tmp (JSON) VALUES
('"http://www.companyurl.com","FoundedYear":"2009","Status":"Public","TagLine":"We build software we believe in","Origi..');
INSERT INTO #tmp (JSON) VALUES (NULL);

Run the test:

SELECT comp.CompanySize
FROM   #tmp tmp
CROSS APPLY tempdb.dbo.GetCompanySize(tmp.JSON) comp

Returns:

CompanySize
-----------
51-200
NULL
NULL

Upvotes: 4

Related Questions