JSON to SQL Server: Convert sub-array of values to column -

i have json like

{       "url": {         "web_categories": [{ "id": 226 } , { "id": 401 }]   },      "account":"basic"   } 

i output in sql column contains ids of web_categories url matched to.

| webcat | |--------| | 226    | | 401    | 

using sql server 2016 / azure sql db's support json, how 1 achieve that?

reproducible example

declare @jsoninfo varchar(max)   set @jsoninfo =n'{       "url": {         "web_categories": [{ "id": 226 }]   },      "account":"basic"   }'   

previous iterations

select * openjson(@jsoninfo)  (webcat varchar(12) '$.url.web_categories.id[0]') select * openjson(@jsoninfo)  (webcat varchar(12) '$.url.web_categories.id') select * openjson(@jsoninfo)  (webcat varchar(12) '$.url.web_categories') select * openjson(@jsoninfo,'$.url.web_categories.id[0]') select * openjson(@jsoninfo,'$.url.web_categories.id') select * openjson(@jsoninfo,'$.url.web_categories') select json_query(@jsoninfo, '$.url.web_categories.id[0]') webcat select json_query(@jsoninfo, '$.url.web_categories.id')    webcat select json_query(@jsoninfo, '$.url.web_categories')       webcat select json_value(@jsoninfo, '$.url.web_categories.id[0]') webcat select json_value(@jsoninfo, '$.url.web_categories.id')    webcat select json_value(@jsoninfo, '$.url.web_categories')       webcat 

using default schema, records returned value column contains json each item in array. json needs processed.

declare @jsoninfo varchar(max)   set @jsoninfo =n'{       "url": {         "web_categories": [{ "id": 226 },{ "id": 411 }]   },      "account":101   }'      select json_value(value,'$.id') ids openjson(@jsoninfo,'$.url.web_categories')