Parse specific value from JSON in SQL Server

Question

I am trying to extract postal_code from Google geocoder JSON (long_name from address_components where types = postal_code, in this case value 1000):

   "results":[
      {
         "address_components":[
            {
               "long_name":"9",
               "short_name":"9",
               "types":[
                  "street_number"
               ]
            },
            {
               "long_name":"Pod bukvami",
               "short_name":"Pod bukvami",
               "types":[
                  "route"
               ]
            },
            {
               "long_name":"Ljubljana",
               "short_name":"Ljubljana",
               "types":[
                  "postal_town"
               ]
            },
            {
               "long_name":"Ljubljana",
               "short_name":"Ljubljana",
               "types":[
                  "administrative_area_level_1",
                  "political"
               ]
            },
            {
               "long_name":"Slovenia",
               "short_name":"SI",
               "types":[
                  "country",
                  "political"
               ]
            },
            {
               "long_name":"1000",
               "short_name":"1000",
               "types":[
                  "postal_code"
               ]
            }
         ],
         "formatted_address":"Pod bukvami 9, 1000 Ljubljana, Slovenia",
         "geometry":{
            "location":{
               "lat":46.0346449,
               "lng":14.4894346
            },
            "location_type":"ROOFTOP",
            "viewport":{
               "northeast":{
                  "lat":46.035993880291493,
                  "lng":14.4907835802915
               },
               "southwest":{
                  "lat":46.0332959197085,
                  "lng":14.4880856197085
               }
            }
         },
         "place_id":"ChIJG19AZRUtZUcRDQni_pW0iLc",
         "plus_code":{
            "compound_code":"2FMQ+VQ Ljubljana, Slovenia",
            "global_code":"8FRP2FMQ+VQ"
         },
         "types":[
            "establishment",
            "lodging",
            "point_of_interest"
         ]
      }
   ],
   "status":"OK"
}

I tried with JSON_VALUE([GoogleJSON],'$.results[0].address_components[5].long_name') but address_components array doesn't have fixed number of elements. I would like to use something like address_components[types="postal_code"].long_name but not sure if this is possible

Thanks, Miljan

Answer 1

NOTE: The JSON posted in the question requires surrounding braces {} to make it valid JSON.

Have you read up on OPENJSON (Transact-SQL) yet? It's easy to use it from a cross apply as shown below...

select address.*
from #Example
cross apply openjson(GoogleJSON, N'$.results[0].address_components') with (
  long_name nvarchar(100),
  short_name nvarchar(100),
  types nvarchar(max) as json
) address
where 'postal_code' in (select [value] from openjson(types, '$'));

Which returns the result...

long_name	short_name	types
1000	1000	[ "postal_code" ]