How to structure nested arrays with postgresql

Question

I'm making a simple multiplayer game using postgres as a database (and node as the BE if that helps). I made the table users which contains all of the user accounts, and a table equipped, which contains all of the equipped items a user has. users has a one -> many relationship with equipped.

I'm running into the situation where I need the data from both tables structured like so:

[
  {
    user_id: 1,
    user_data...
    equipped: [
      { user_id: 1, item_data... },
      ...
    ],
  },
  {
    user_id: 2,
    user_data...
    equipped: [
      { user_id: 2, item_data... },
      ...
    ],
  },
]

Is there a way to get this data in a single query? Is it a good idea to get it in a single query?

EDIT: Here's my schemas

  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)
  );

Yes, it is possible to get that in a single query. This looks like json to me. Maybe look into the functions array_agg() and jsonb_build_object(). Without more information such as example input and example tables it will be hard for us to get started on a query for you. — J Spratt
– J Spratt, Commented Aug 2, 2019 at 19:23
I posted json because my database driver (node-postgres) outputs JSON l, so that's what I posted. I'll update the post with the tables in a bit. I think the Crux of the question is: how do I query for a list of lists? — Danman
– Danman, Commented Aug 2, 2019 at 19:42

Danman · Accepted Answer · 2019-08-03 00:43:12Z

Okay so what I was looking for was closer to postgresql json aggregate, but I didn't know what to look for.

Based on my very limited SQL experience, the "classic" way to handle this would just to do a simple JOIN query on the database like so:

SELECT users.username, equipment.slot, equipment.inventory_id
FROM users
LEFT JOIN equipment ON users.user_id = equipment.user_id;

This is nice and simple, but I would need to merge these tables in my server before sending them off.

Thankfully postgres lets you aggregate rows into a JSON array, which is exactly what I needed (thanks @j-spratt). My final* query looks like:

SELECT users.username,
json_agg(json_build_object('slot', equipment.slot, 'inventory_id', equipment.inventory_id))
FROM users
LEFT JOIN equipment ON users.user_id = equipment.user_id
GROUP BY users.username;

Which returns in exactly the format I was looking for.

Collectives™ on Stack Overflow

How to structure nested arrays with postgresql

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related