title: Access JSON_VALUE from SQL Column containing JSON String with Arrays
body: <p>Looking to use JSON_VALUE to access some data within a JSON column in an SQL Server table.</p>
  <p>I have a table (tblMyTable) that has the following columns and data;</p>
  <pre><code>| Column1  | JSONColumn     |
  | -------- | -------------- |
  | 1        | {some JSON}    |
  </code></pre>
  <p>Where {some JSON} looks as follows;</p>
  <pre>
  
      [
         {
            "RoleName":"Client",
            "Roles":[
               {
                  "ContactID":21568,
                  "ContactName":"FullName1"
               },
               {
                  "ContactID":31568,
                  "ContactName":"FullName2"
               }
            ]
         },
         {
            "RoleName":"Owner",
            "Roles":[
               {
                  "ContactID":1,
                  "ContactName":"Billy Buxton"
               }
            ]
         }
      ]
  
  </pre>
  <p>I am wanting to use something like this to access the ContactName of Billy Buxton;</p>
  <pre><code>
      SELECT JSON_VALUE(JSONColumn, '$[Owner].Roles[0].ContactName') AS ContactName
      FROM tblMyTable
      WHERE Column1=1
  
  </code></pre>
  <p>RESULT:</p>
  <pre><code>| ContactName |
  | ----------- |
  | Billy Buxton|
  </code></pre>
  <p>The issue is that I don't know where the Owner RoleName is in the JSON so I can't use;
  '$[0].Roles[0].ContactName' as it might be '$[1].Roles' or '$[10].Roles'.</p>
  <p>Is there a way to do this by specifying the 'Owner' or 'Client' RoleName?</p>
  <p>In addition, if I use the following I would get the array of ContactNames;</p>
  <pre><code>
      SELECT JSON_QUERY(JSONColumn, '$[Client].Roles.ContactName') AS ContactNames
      FROM tblMyTable
      WHERE Column1=1
  
  </code></pre>
  <p>RESULT:</p>
  <pre><code>| ContactNames       |
  | ------------------ |
  | FullName1,FullName2|
  </code></pre>
  <p>I am able to change the structure of the JSON if needed, and would consider using OpenJSON if that works with specifying a path (eg; $[Owner].Roles[0].ContactName or similar)</p>
  <p>I had come up with the following option, but it's using OPENJSON which is much messier than using JSON_VALUE or JSON_QUERY;</p>
  <pre><code>
      SELECT Y.RoleName,STRING_AGG(Z.ContactName,',') AS ContactName
      FROM (
          SELECT  '[{&quot;RoleName&quot;:&quot;Client&quot;,&quot;Roles&quot;:[{&quot;ContactID&quot;:21568,&quot;ContactName&quot;:&quot;FullName1&quot;},{&quot;ContactID&quot;:31568,&quot;ContactName&quot;:&quot;FullName2&quot;}]},{&quot;RoleName&quot;:&quot;Owner&quot;,&quot;Roles&quot;:[{&quot;ContactID&quot;:1,&quot;ContactName&quot;:&quot;Billy Buxton&quot;}]}]' AS Roles
      )X
      CROSS APPLY OPENJSON (X.Roles) 
      WITH (
          RoleName VarChar(50),
          Roles nVarChar(MAX) AS JSON
          )Y
      CROSS APPLY OPENJSON (Y.Roles)
      WITH (
          ContactID   BigInt,
          ContactName VarChar(255)
      )Z
      WHERE Y.RoleName='Client'
      GROUP BY Y.RoleName
  
  </code></pre>
  <p>RESULT:</p>
  <pre><code>| RoleName | ContactName        |
  | -------- | ------------------ |
  | Client   | FullName1,FullName2|
  </code></pre>
  <p>Thanks in advance for any assistance you can provide.</p>