title: json sql with list items
body: <p>I have json stored in sql2016. when I run a query parts of the json returns value and other parts don't return value.
  here is a json stored in table dbo.Trades
  The datatype is as follows</p>
  <pre><code>CREATE TABLE [dbo].[Trades](
  [_id] [int] IDENTITY(1,1) NOT NULL,
  [trade] [nvarchar](max) NULL,
  </code></pre>
  <p>My json is formatted as below and stored in trade column</p>
  <pre><code>{
    &quot;direction&quot;: &quot;Short&quot;,
    &quot;Powerbars&quot;: 2,
    &quot;PowerbarsTime&quot;: [
      &quot;2016-11-10T20:25:32.481424-05:00&quot;,
      &quot;2016-11-10T20:44:01.8993031-05:00&quot;
    ]
  }
  </code></pre>
  <p>Since Powerbarstime is a list in the json, I have to provide the index to print it. however I don't know how many  entries will eb there in the list. Is there a way to merge the list which is part of the json and return a comma delimited column?</p>
  <p>I have to do this to print</p>
  <pre><code>SELECT        _id, 
  JSON_VALUE(trade, '$.Powerbars') AS powerbars,
  JSON_VALUE(trade, '$.PowerbarsTime[0]') AS PowerbarsTime1,
  JSON_VALUE(trade, '$.PowerbarsTime[1]') AS PowerbarsTime2
  FROM            dbo.Trades
  order by _id desc
  </code></pre>
  <p>I get the 2 items on the list that I hardcode into the sql.</p>
  <p><a href="https://i.stack.imgur.com/bSKa7.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/bSKa7.png" alt="results" /></a></p>
  <p>How can I merge all the list items under <code>JSON_VALUE(trade, '$.PowerbarsTime')</code> so I can see it all as 1 comma delimited column.</p>