title: How to convert SQL rows to an array of json objects in Azure Data Factory?
body: <p>I'm working on a <strong>Azure Data Factory Pipeline</strong> and have following challenge currently:
  I'd like to make an API call (Post) which requires some data with the syntax of <code>array</code> and in it, multiple <code>objects</code>.</p>
  <p>Now - both, the data retrieving (from <code>SQL</code> db) and <code>API</code> call work when used independently (In case of the API call: I've been using hardcoded mock data for the body). The challenge is in connecting both of them. That means:
  I'd like to get multiple rows out of a SQL table, convert them to the required json structure  and fill that data then into the API call.
  See picture below:</p>
  <p><a href="https://i.stack.imgur.com/QniHr.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/QniHr.png" alt="enter image description here" /></a></p>
  <p>In simple steps explained again:</p>
  <ol>
  <li>Get rows from SQL table</li>
  <li>Convert each row into an object e.g. <code>{ &quot;somekey&quot;: valueOfRow}</code></li>
  <li>Collect all objects in an <code>array</code></li>
  <li>Provide <code>array</code> to <code>API</code> call</li>
  </ol>
  <p>I'm just unsure how to proceed with the connection.</p>
  <p><strong>Additional Information</strong></p>
  <p>As requested, some further detailed information.</p>
  <p>Currently the API call uses following hardcoded mockdata:</p>
  <pre class="lang-json prettyprint-override"><code>[{&quot;idType&quot;: &quot;ID_ISIN&quot;, &quot;idValue&quot;: &quot;US0123456789&quot; }]
  </code></pre>
  <p>From the dataflow I'll get rows with one column called <code>isin</code> (with row values such as <code>US0123456789</code>)</p>
  <p>The goal is to fill API's body dynamically such that it receives something like this:</p>
  <pre class="lang-json prettyprint-override"><code>[
      {
          &quot;idType&quot;: &quot;ID_ISIN&quot;, 
          &quot;idValue&quot;: &quot;US0123456789&quot; 
      },
      {
          &quot;idType&quot;: &quot;ID_ISIN&quot;, 
          &quot;idValue&quot;: &quot;US9876543210&quot; 
      },
      {...}
  
  ]
  </code></pre>
  <p>I saw that one can achieve something similiar with <code>SQL</code> query - see: <a href="https://docs.microsoft.com/en-us/azure/azure-sql/database/json-features?view=azuresql" rel="nofollow noreferrer">https://docs.microsoft.com/en-us/azure/azure-sql/database/json-features?view=azuresql</a></p>
  <p>But I'd miss the part <code>&quot;idType&quot;: &quot;ID_ISIN&quot;</code> in each row/object.</p>