How to parse a complicated string in SQL Server -
i'm trying figure out best way parse complex json object stored string in sql server.
my table has following information:
logid | content ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 55271413 | {"licenseplate":"","freighthaulerproviderxid":"abc","freightproviderreferencenumber":"5218912","carrierscac":"xyz","latitude":33.595555,"longitude":-85.854722,"streetaddress1":"","streetaddress2":"","city":"","state":"","postalcode":"","country":""} 55271414 | {"licenseplate":"","freighthaulerproviderxid":"abc","freightproviderreferencenumber":"5218944","carrierscac":"xyz","latitude":37.996666,"longitude":-78.314444,"streetaddress1":"","streetaddress2":"","city":"","state":"","postalcode":"","country":""} 55271415 | {"licenseplate":"","freighthaulerproviderxid":"abc","freightproviderreferencenumber":"5219079","carrierscac":"yzb","latitude":34.027500,"longitude":-117.522222,"streetaddress1":"","streetaddress2":"","city":"","state":"","postalcode":"","country":""} 55271416 | {"licenseplate":"","freighthaulerproviderxid":"abc","freightproviderreferencenumber":"5219020","carrierscac":"xyz","latitude":37.754722,"longitude":-121.144166,"streetaddress1":"","streetaddress2":"","city":"","state":"","postalcode":"","country":""} 55271417 | {"licenseplate":"","freighthaulerproviderxid":"abc","freightproviderreferencenumber":"5218911","carrierscac":"xyz","latitude":40.585833,"longitude":-91.425000,"streetaddress1":"","streetaddress2":"","city":"","state":"","postalcode":"","country":""} 55271418 | {"licenseplate":"","freighthaulerproviderxid":"abc","freightproviderreferencenumber":"5218785","carrierscac":"xyz","latitude":30.747500,"longitude":-85.270277,"streetaddress1":"","streetaddress2":"","city":"","state":"","postalcode":"","country":""} 55271426 | {"licenseplate":"","freighthaulerproviderxid":"abc","freightproviderreferencenumber":"5219044","carrierscac":"xyz","latitude":33.598333,"longitude":-97.936388,"streetaddress1":"","streetaddress2":"","city":"","state":"","postalcode":"","country":""} i'm trying parse each string , place inside new column name of json attribute column name, , corresponding value row value.
for example, here results i'm looking below each row:
logid | licenseplate | freighthaulerproviderxid | freightproviderreferencenumber | carrierscac | latitude | longitude | streetaddress1 | streetaddress2 | city | state | postalcode | country ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 55271413 | | abc | 5218912 | xyz | 33.595555 | -85.854722 | | | | | | i've tried parsing using pretty bad sql logic. entire string, grab sub-string, , manually assign column name. isn't solution scalability , performance.
select distinct substring(lcon.content, charindex('carrierscac', lcon.content)+14, charindex('city',lcon.content) - charindex('carrierscac', lcon.content) + len('city')-21) 'carrierscac', substring(lcon.content, charindex('latitude', lcon.content)+10, charindex('longitude',lcon.content) - charindex('latitude', lcon.content) + len('longitude')-21) 'latitude', substring(lcon.content, charindex('longitude', lcon.content)+11, charindex('positioneventtype',lcon.content) - charindex('longitude', lcon.content) + len('"positioneventtype')-31) 'longitude' acg.logcontext lcon lcon.content '%xyz%' any appreciated seem stumped after researching techniques better half of day.
thanks!
with of parse function , 2 cross applies...
declare @yourtable table (logid int,content varchar(max)) insert @yourtable values (55271413,'{"licenseplate":"","freighthaulerproviderxid":"abc","freightproviderreferencenumber":"5218912","carrierscac":"xyz","latitude":33.595555,"longitude":-85.854722,"streetaddress1":"","streetaddress2":"","city":"","state":"","postalcode":"","country":""}'), (55271414,'{"licenseplate":"","freighthaulerproviderxid":"abc","freightproviderreferencenumber":"5218944","carrierscac":"xyz","latitude":37.996666,"longitude":-78.314444,"streetaddress1":"","streetaddress2":"","city":"","state":"","postalcode":"","country":""}'), (55271415,'{"licenseplate":"","freighthaulerproviderxid":"abc","freightproviderreferencenumber":"5219079","carrierscac":"yzb","latitude":34.027500,"longitude":-117.522222,"streetaddress1":"","streetaddress2":"","city":"","state":"","postalcode":"","country":""}'), (55271416,'{"licenseplate":"","freighthaulerproviderxid":"abc","freightproviderreferencenumber":"5219020","carrierscac":"xyz","latitude":37.754722,"longitude":-121.144166,"streetaddress1":"","streetaddress2":"","city":"","state":"","postalcode":"","country":""}'), (55271417,'{"licenseplate":"","freighthaulerproviderxid":"abc","freightproviderreferencenumber":"5218911","carrierscac":"xyz","latitude":40.585833,"longitude":-91.425000,"streetaddress1":"","streetaddress2":"","city":"","state":"","postalcode":"","country":""}'), (55271418,'{"licenseplate":"","freighthaulerproviderxid":"abc","freightproviderreferencenumber":"5218785","carrierscac":"xyz","latitude":30.747500,"longitude":-85.270277,"streetaddress1":"","streetaddress2":"","city":"","state":"","postalcode":"","country":""}'), (55271426,'{"licenseplate":"","freighthaulerproviderxid":"abc","freightproviderreferencenumber":"5219044","carrierscac":"xyz","latitude":33.598333,"longitude":-97.936388,"streetaddress1":"","streetaddress2":"","city":"","state":"","postalcode":"","country":""}') select logid ,licenseplate = max(case when item='licenseplate' value else null end) ,freighthaulerproviderxid = max(case when item='freighthaulerproviderxid' value else null end) ,freightproviderreferencenumber = max(case when item='freightproviderreferencenumber' value else null end) ,carrierscac = max(case when item='carrierscac' value else null end) ,latitude = max(case when item='latitude' value else null end) ,longitude = max(case when item='longitude' value else null end) ,streetaddress1 = max(case when item='streetaddress1' value else null end) ,streetaddress2 = max(case when item='streetaddress2' value else null end) ,city = max(case when item='city' value else null end) ,state = max(case when item='state' value else null end) ,postalcode = max(case when item='postalcode' value else null end) ,country = max(case when item='country' value else null end) ( select logid ,item = max(case when retseq=1 retval else null end) ,value = max(case when retseq=2 retval else null end) ( select a.logid ,grp = b.retseq ,c.* @yourtable cross apply (select retseq,retval=replace(replace(replace(retval,'"',''),'{',''),'}','') [dbo].[udf-str-parse](a.content,',') ) b cross apply (select * [dbo].[udf-str-parse](b.retval,':') ) c ) n group logid,grp ) f group logid returns
the udf if needed
create function [dbo].[udf-str-parse] (@string varchar(max),@delimiter varchar(10)) returns table return ( select retseq = row_number() on (order (select null)) ,retval = ltrim(rtrim(b.i.value('(./text())[1]', 'varchar(max)'))) (select x = cast('<x>'+ replace(@string,@delimiter,'</x><x>')+'</x>' xml).query('.')) cross apply x.nodes('x') b(i) ); --select * [dbo].[udf-str-parse]('dog,cat,house,car',',') --select * [dbo].[udf-str-parse]('john cappelletti here',' ')
Comments
Post a Comment