Using JSON in PostgreSQL to handle complex aggregations

I always find it very difficult when I have to deal with complex aggregations in SQL that involve multiple tables as a requirement that is usually coming from the upper layer (frontend). I learned that most DBs today do support JSON data types, JSON functions, and JSON operators. I will show you how to use JSON in PostgreSQL to handle complex aggregations and queries.

Example tables

Following is the example tables that I will use in this article:

CREATE TABLE IF NOT EXISTS weapons (
  id SERIAL PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  damage INT NOT NULL
);
 
CREATE TABLE IF NOT EXISTS adventurers (
  id SERIAL PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  level INT NOT NULL DEFAULT 1,
  experience INT NOT NULL,
  health INT NOT NULL
);
 
CREATE TABLE IF NOT EXISTS inventory (
  adventurer_id INTEGER REFERENCES adventurers(id) ON DELETE CASCADE,
  weapon_id INTEGER REFERENCES weapons(id) ON DELETE CASCADE,
  quantity INT NOT NULL DEFAULT 1,
  equipped BOOLEAN NOT NULL DEFAULT FALSE,
  PRIMARY KEY (adventurer_id, weapon_id)
);
 
INSERT INTO weapons (name, damage) VALUES
  ('Sword', 10),
  ('Axe', 15),
  ('Bow', 8);
 
INSERT INTO adventurers (name, level, experience, health) VALUES
  ('Archer', 1, 0, 100),
  ('Warrior', 1, 0, 120),
  ('Mage', 1, 0, 80);
 
INSERT INTO inventory (adventurer_id, weapon_id, quantity, equipped) VALUES
  (1, 1, 1, TRUE),
  (1, 2, 2, FALSE),
  (1, 3, 5, FALSE);

Returning Single JSON row

I have the following JSON I want to return that I map to in Go:

type Adventurer struct {
	ID             int64  `json:"id"`
	Name           string `json:"name"`
	Level          int64  `json:"level"`
	Experience     int64  `json:"experience"`
	Health         int64  `json:"health"`
	EquippedWeapon *struct {
		ID     int64  `json:"id"`
		Name   string `json:"name"`
		Damage int64  `json:"damage"`
	} `json:"equippedWeapon"`
	Inventory []struct {
		ID       int64  `json:"id"`
		Name     string `json:"name"`
		Damage   int64  `json:"damage"`
		Quantity int64  `json:"quantity"`
	} `json:"inventory"`
}

Getting one row

SELECT json_build_object(
  'id', a.id,
  'name', a.name,
  'level', a.level,
  'experience', a.experience,
  'health', a.health,
  'equippedWeapon', (
    SELECT json_build_object(
      'id', w.id,
      'name', w.name,
      'damage', w.damage
    )
    FROM inventory i
    JOIN weapons w ON i.weapon_id = w.id
    WHERE i.adventurer_id = a.id AND i.equipped = TRUE
    LIMIT 1
  ),
  'inventory', (
    SELECT json_agg(
      json_build_object(
        'id', w.id,
        'name', w.name,
        'damage', w.damage,
        'quantity', i.quantity
      )
    )
    FROM inventory i
    JOIN weapons w ON i.weapon_id = w.id
    WHERE i.adventurer_id = a.id
		AND equipped = FALSE
  )
)
FROM adventurers a
WHERE a.id = $1;

Fairly simple query! Let's see how it is done when returning multiple rows.

Getting multiple rows

SELECT
	json_agg(
		json_build_object(
			'id', a.id,
			'name', a.name,
			'level', a.level,
			'experience', a.experience,
			'health', a.health,
			'equippedWeapon', (
				SELECT json_build_object(
					'id', w.id,
					'name', w.name,
					'damage', w.damage
				)
				FROM inventory i
				JOIN weapons w ON i.weapon_id = w.id
				WHERE i.adventurer_id = a.id AND i.equipped = TRUE
				LIMIT 1
			),
			'inventory', (
				SELECT json_agg(
					json_build_object(
						'id', w.id,
						'name', w.name,
						'damage', w.damage,
						'quantity', i.quantity
					)
				)
				FROM inventory i
				JOIN weapons w ON i.weapon_id = w.id
				WHERE i.adventurer_id = a.id
				AND equipped = FALSE
			)
		) ORDER BY a.id DESC
	)
FROM adventurers a

We just add json_agg to the query and we are done. The result will be a JSON array of objects.

Conclusion

Using JSON in PostgreSQL is a powerful way to handle complex aggregations and queries. It allows you to return data in a structured format that can be easily consumed by your application. The examples provided demonstrate how to use JSON functions and operators to achieve this. If you have any questions or need further assistance, feel free to reach out. I hope this article helps you in your journey to master PostgreSQL and JSON.