Extract Array Element From JSON Using T-SQL

Question

I have a following response string from Google Geocoding API stored in a SQL Server database:

{
   "results":[
      {
         "address_components":[
            {
               "long_name":"Khalifa City",
               "short_name":"Khalifa City",
               "types":[
                  "political",
                  "sublocality",
                  "sublocality_level_1"
               ]
            },
            {
               "long_name":"Abu Dhabi",
               "short_name":"Abu Dhabi",
               "types":[
                  "locality",
                  "political"
               ]
            },
            {
               "long_name":"Abu Dhabi",
               "short_name":"Abu Dhabi",
               "types":[
                  "administrative_area_level_1",
                  "political"
               ]
            },
            {
               "long_name":"United Arab Emirates",
               "short_name":"AE",
               "types":[
                  "country",
                  "political"
               ]
            }
         ],
         ...
      }
   ],
   "status":"OK"
}

My task is to extract Country and City from the above JSON. I checked the data and it seems that Geocoding API does not always return 4 elements in address_component node, so I need to get element in the array where types contain administrative_area_level_1 for the city, for example, which should logically be something like this:

JSON_QUERY([Json], '$.results[0].address_components<where types = administrative_area_level_1>.short_name')

Answer 1

This is how I have addressed this in the past. You can run this within SSMS:

DECLARE @json AS VARCHAR(1000) = '{ "results":[ { "address_components":[
    { "long_name":"Khalifa City", "short_name":"Khalifa City", "types":[ "political", "sublocality", "sublocality_level_1" ] },
    { "long_name":"Abu Dhabi", "short_name":"Abu Dhabi", "types":[ "locality", "political" ] },
    { "long_name":"Abu Dhabi", "short_name":"Abu Dhabi", "types":[ "administrative_area_level_1", "political" ] },
    { "long_name":"United Arab Emirates", "short_name":"AE", "types":[ "country", "political" ] }
] } ], "status":"OK" }';

SELECT
    Addresses.long_name, Addresses.short_name, Addresses.[types]
FROM OPENJSON ( @json, '$.results' ) WITH (
    addresses NVARCHAR(MAX) '$.address_components' AS JSON
) AS j
CROSS APPLY (

    SELECT * FROM OPENJSON ( j.addresses ) WITH (
        long_name VARCHAR(50) '$.long_name',
        short_name VARCHAR(50) '$.short_name',
        [types] NVARCHAR(MAX) '$.types' AS JSON
    ) AS Names
    CROSS APPLY OPENJSON ( [types] ) AS [Types]
    WHERE [Types].[value] = 'administrative_area_level_1'

) AS Addresses;

Returns

+-----------+------------+------------------------------------------------+
| long_name | short_name |                     types                      |
+-----------+------------+------------------------------------------------+
| Abu Dhabi | Abu Dhabi  | [ "administrative_area_level_1", "political" ] |
+-----------+------------+------------------------------------------------+

Answer 2

If I understand the question and you want to parse the input JSON (even when the $.results JSON array has more than one item), the following approach may help:

JSON:

DECLARE @json nvarchar(max) = N'{
   "results":[
      {
         "address_components":[
            {"long_name":"Khalifa City", "short_name":"Khalifa City", "types":["political", "sublocality", "sublocality_level_1"]},
            {"long_name":"Abu Dhabi", "short_name":"Abu Dhabi", "types":["locality", "political"]},
            {"long_name":"Abu Dhabi", "short_name":"Abu Dhabi", "types":["administrative_area_level_1", "political"]},
            {"long_name":"United Arab Emirates", "short_name":"AE", "types":["country", "political"]}
         ]
      }
   ],
   "status":"OK"
}'

Statement:

SELECT j2.long_name, j2.short_name
FROM OPENJSON(@json, '$.results') j1
CROSS APPLY OPENJSON(j1.value, '$.address_components') WITH (
   long_name varchar(100) '$.long_name',
   short_name varchar(100) '$.short_name',
   types nvarchar(max) '$.types' AS JSON
) j2
CROSS APPLY OPENJSON(j2.types) j3
WHERE j3.[value] = 'administrative_area_level_1'

Output:

long_name   short_name
----------------------
Abu Dhabi   Abu Dhabi