title: JSON to SQL Server: Convert sub-array of values to column
body: <p>I have JSON like</p>
  
  <pre><code>{  
      "url": {
          "web_categories": [{ "id": 226 } , { "id": 401 }]
    },  
     "account":"Basic"  
  }
  </code></pre>
  
  <p>I would like to output in SQL a column that contains the IDs of all <code>web_categories</code> a URL was matched to. </p>
  
  <pre><code>| webcat |
  |--------|
  | 226    |
  | 401    |
  </code></pre>
  
  <p>Using SQL Server 2016 / Azure SQL DB's support for JSON, how would one achieve that?</p>
  
  <h2>Reproducible example</h2>
  
  <pre><code>DECLARE @jsonInfo VARCHAR(MAX)  
  SET @jsonInfo =N'{  
      "url": {
          "web_categories": [{ "id": 226 }]
    },  
     "account":"Basic"  
  }'  
  </code></pre>
  
  <h2>Previous iterations</h2>
  
  <pre><code>select * from openjson(@jsonInfo)  with (webcat varchar(12) '$.url.web_categories.id[0]')
  select * from openjson(@jsonInfo)  with (webcat varchar(12) '$.url.web_categories.id')
  select * from openjson(@jsonInfo)  with (webcat varchar(12) '$.url.web_categories')
  select * from openjson(@jsonInfo,'$.url.web_categories.id[0]')
  select * from openjson(@jsonInfo,'$.url.web_categories.id')
  select * from 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
  </code></pre>