title: Extracting a single value from a json array in sql server
body: <p>I am using MS SQL server to get a search result in json format there is only ever 1 row returned in my use case but they designed this as a search tool so you can return more than one value hence the array. The issue I am having is extracting the id value from the array that is returned.</p>
  
  <p>json @response (Array):</p>
  
  <pre><code>{"hits":[{"id":1320172,"email":"xyz@domain.eu","first_name":"IMA","last_name":"TESTERTOO","created":"2018-12-12T11:52:58+00:00","roles":["Learner"],"status":true}],"total":1}
  </code></pre>
  
  <p>I have tried a number of things but I can't seem to get the path right.</p>
  
  <pre><code>SET @MyUserid = JSON_QUERY(@Reponse, '$.hits[0].id')  
  
  SET @MyUserid  =JSON_VALUE(@Reponse,'$.hits[0].id')
  
  SET @MyUserid = JSON_QUERY(@Reponse, '$.id')
  </code></pre>
  
  <p>On most examples I have found the json is not a single line array so I feel like I am missing something there. I'm inexperienced with working with json so any help would be greatly appreciated.</p>