CODEWITHSUNDEEP
jsonsql-serversql-server-2012
capnam
capnam

Reputation: 437

What is the best way to import selective properties from JSON into a sql server table

I have a json file which contains data I need to import into a table, the problem is I only want the Latitude, Longitude and preferredGazzeteerName properties.

Here is how my json data looks like :

[
  {
    "MRGID": 2375,
    "gazetteerSource": "Nomenclature des espaces maritimes/List of maritime areas",
    "placeType": "Strait",
    "latitude": 51.036666666667,
    "longitude": 1.5486111111111,
    "minLatitude": 49.71788333,
    "minLongitude": 0.238905863,
    "maxLatitude": 51.78156033,
    "maxLongitude": 2.744909289,
    "precision": 21000,
    "preferredGazetteerName": "Dover Strait",
    "preferredGazetteerNameLang": "English",
    "status": "standard",
    "accepted": 2375
  },
  {
    "MRGID": 2376,
    "gazetteerSource": "The Times comprehensive atlas of the world. 10th ed. Times Books: London, UK. ISBN 0-7230-0792-6. 67, 220, 124 plates pp.,",
    "placeType": "Strait",
    "latitude": 54.604722222222,
    "longitude": 11.220833333333,
    "minLatitude": null,
    "minLongitude": null,
    "maxLatitude": null,
    "maxLongitude": null,
    "precision": 40000,
    "preferredGazetteerName": "Femer Baelt",
    "preferredGazetteerNameLang": "English",
    "status": "standard",
    "accepted": 2376
  }]

and table enter image description here

The preferredGazetteerName values will be inserted at strait name column.

Upvotes: 1

Views: 80

Answers (1)

John Cappelletti
John Cappelletti

Reputation: 81990

Here is one approach where we split the JSON string with a delimiter of '{' then it just becomes a matter of string manipulation

Example

Declare @S varchar(max) ='... your JSON String ...'

Select Name = left(Name,charindex(',',Name+',')-1)
      ,Lat  = try_convert(float,left(Lat,charindex(',',Lat+',')-1))
      ,Lng  = try_convert(float,left(Lng,charindex(',',Lng+',')-1))
 From  (
        Select Name = ltrim(replace(substring(RetVal,nullif(charindex('"preferredGazetteerName"',RetVal),0)+25,75),'"',''))
              ,Lat  = ltrim(substring(RetVal,nullif(charindex('"latitude"',RetVal),0) +11,25))
              ,Lng  = ltrim(substring(RetVal,nullif(charindex('"longitude"',RetVal),0)+12,25))
         From  [dbo].[tvf-Str-Parse](@S,'{') 
       ) A
 Where Name is not null

Returns

Name            Lat               Lng
Dover Strait    51.036666666667   1.5486111111111
Femer Baelt     54.604722222222   11.220833333333

The Split/Parse TVF if Interested

CREATE FUNCTION [dbo].[tvf-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)
);

Upvotes: 2

Related Questions