title: How to structure nested arrays with postgresql
body: <p>I'm making a simple multiplayer game using postgres as a database (and node as the BE if that helps). I made the table <code>users</code> which contains all of the user accounts, and a table <code>equipped</code>, which contains all of the equipped items a user has. <code>users</code> has a one -> many relationship with <code>equipped</code>.</p>
  
  <p>I'm running into the situation where I need the data from both tables structured like so:</p>
  
  <pre><code>[
    {
      user_id: 1,
      user_data...
      equipped: [
        { user_id: 1, item_data... },
        ...
      ],
    },
    {
      user_id: 2,
      user_data...
      equipped: [
        { user_id: 2, item_data... },
        ...
      ],
    },
  ]
  </code></pre>
  
  <p>Is there a way to get this data in a single query? Is it a good idea to get it in a single query?</p>
  
  <p>EDIT: Here's my schemas</p>
  
  <pre class="lang-sql prettyprint-override"><code>  CREATE TABLE IF NOT EXISTS  users (
      user_id SERIAL PRIMARY KEY,
      username VARCHAR(100) UNIQUE NOT NULL,
      password VARCHAR(100) NOT NULL,
      email VARCHAR(100) NOT NULL,
      created_on TIMESTAMP NOT NULL DEFAULT NOW(),
      last_login TIMESTAMP,
      authenticated BOOLEAN NOT NULL DEFAULT FALSE,
      reset_password_hash UUID
    );
    CREATE TABLE IF NOT EXISTS equipment (
      equipment_id SERIAL PRIMARY KEY NOT NULL,
      inventory_id INTEGER NOT NULL REFERENCES inventory (inventory_id) ON DELETE CASCADE,
      user_id INTEGER NOT NULL REFERENCES users (user_id) ON DELETE CASCADE,
      slot equipment_slot NOT NULL,
      created_on TIMESTAMP NOT NULL DEFAULT NOW(),
      CONSTRAINT only_one_item_per_slot UNIQUE (user_id, slot)
    );
  </code></pre>