title: How to improve this query using array or json constructor?
body: <p>how are you?</p>
  <p>I am wondering how to improve this query to return something better to work with. Let me show the tables, the current query and my idea first:</p>
  <h2>Tables</h2>
  <p>users</p>
  <p><a href="https://i.stack.imgur.com/gV18Z.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/gV18Z.png" alt="enter image description here" /></a></p>
  <p>nfts</p>
  <p>here owner_id is a fk to users.id</p>
  <p><a href="https://i.stack.imgur.com/QghRH.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/QghRH.png" alt="enter image description here" /></a></p>
  <p>users_nfts (here I save all the creators of the nft, one nft could have more than one creator. This is to calculate royalties later)</p>
  <p><a href="https://i.stack.imgur.com/cAJ2e.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/cAJ2e.png" alt="enter image description here" /></a></p>
  <h2>Current query and explanation</h2>
  <p>To be able to code a &quot;buy&quot; nft process (in nodejs) I want to retrieve some data about the nft to buy:</p>
  <ul>
  <li>Its price</li>
  <li>Its current owner</li>
  <li>The creators (to calculate the royalties and update their balances)</li>
  </ul>
  <pre class="lang-sql prettyprint-override"><code>SELECT nfts.id, price, owner_id, owner.balance as owner_balance, creators.user_id, users.balance
  FROM nfts
  INNER JOIN users_nfts as creators
  ON nfts.id = creators.nft_id
  INNER JOIN users
  ON creators.user_id = users.id
  INNER JOIN users as owner
  ON nfts.owner_id = owner.id
  WHERE nfts.id = ${nft_id}
  </code></pre>
  <p>The query works but it's horrible because it retrieves me repeated data (this is what I want to solve). The red square means the repeated data.</p>
  <p><a href="https://i.stack.imgur.com/Wp4Dw.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/Wp4Dw.png" alt="enter image description here" /></a></p>
  <h2>What I would like to achieve</h2>
  <p>I would like to make a query so all the data about the NFT comes in one row. To do that, I need to retrieve the user_id and balance inside an array of tuples or in a json.
  The result in my backend could be something like (any ideas here are welcome):</p>
  <pre class="lang-js prettyprint-override"><code>{
      &quot;id&quot;: &quot;ea850c65-818e-40bd-bb06-af69eaeda4a6&quot;, // nft id
      &quot;price&quot;: 42,
      &quot;owner_id&quot;: &quot;1134e9e0-02ae-4567-9adf-220ead36a6ef&quot;,
      &quot;owner_balance&quot;: 100,
      &quot;creators&quot;: [
          {
              &quot;user_id&quot;: &quot;1134e9e0-02ae-4567-9adf-220ead36a6ef&quot;,
              &quot;balance&quot;: 100,
          },
          {
              &quot;user_id&quot;: &quot;2134e9e0-02ae-4567-9adf-220ead36a6ea&quot;,
              &quot;balance&quot;: 35,
          },
      ],
  },
  </code></pre>
  <p>Thanks in advance for any tips :)</p>