How I Reduced Laravel N+1 Queries by 83% Using JSON Aggregation

The Problem: N+1 Queries Are Killing Your API Performance

If you've worked with Laravel's Eloquent ORM, you've probably encountered the N+1 query problem. Even with eager loading (with()), Laravel generates multiple queries when loading related collections.

Here's a typical scenario:

// This looks innocent, but...
$partners = Partner::with(['profile', 'country', 'promocodes'])->get();

// Laravel generates:
// 1 query for partners
// 1 query for profiles  
// 1 query for countries
// 1 query for promocodes
// Total: 4 queries

For 2,000 partners, this pattern generates 43 queries and takes 287ms with 44MB memory usage.

The Traditional Solution: Eager Loading Isn't Enough

Eager loading helps, but it still generates separate queries for each relationship. For read-heavy APIs and dashboards, this becomes a bottleneck:

Multiple database round-trips
Large result sets to hydrate
High memory consumption
Unpredictable query counts as relations grow

A Better Solution: JSON Aggregation

I built laravel-aggregated-queries to solve this problem using database-native JSON aggregation (MySQL 8+ / PostgreSQL 12+).

Instead of multiple queries, we leverage JSON_OBJECT() and JSON_ARRAYAGG() to combine everything into a single SQL statement:

use Rgalstyan\LaravelAggregatedQueries\Traits\HasAggregatedQueries;

class PartnerRepository extends Repository
{
    use HasAggregatedQueries;
    
    public function getAllWithRelations()
    {
        return $this->aggregatedQuery()
            ->withJsonRelation('profile', ['id', 'name', 'email'])
            ->withJsonRelation('country', ['id', 'name', 'code'])
            ->withJsonCollection('promocodes', ['id', 'code', 'discount'])
            ->get();
    }
}

Result: 1 query instead of 4.

Real-World Benchmark Results

I tested this approach with 2,000 partners, each having a profile, country, and multiple promocodes:

Approach	Time	Memory	Queries
Traditional Eloquent	287ms	44MB	43
JSON Aggregation	48ms	3.4MB	1

Improvements:

⚡ 83.3% faster execution time
💾 92.3% less memory consumption
🔢 97.7% fewer queries (43 → 1)

When Should You Use This?

This approach is perfect for:

✅ Read-heavy endpoints - APIs, dashboards, reports
✅ Multiple collections - 3+ relations per query
✅ DTO/array responses - No need for full Eloquent models
✅ Predictable performance - Exact query count guaranteed

Not recommended for:

Write-heavy operations (use standard Eloquent)
Complex business logic requiring Eloquent models
Simple single-relation queries (eager loading is fine)

How It Works Under The Hood

The package generates optimized SQL like this:

SELECT 
    partners.*,
    JSON_OBJECT('id', profile.id, 'name', profile.name) AS profile,
    JSON_OBJECT('id', country.id, 'name', country.name) AS country,
    (SELECT JSON_ARRAYAGG(
        JSON_OBJECT('id', id, 'code', code, 'discount', discount)
    ) FROM promocodes WHERE partner_id = partners.id) AS promocodes
FROM partners
LEFT JOIN profiles AS profile ON profile.id = partners.profile_id
LEFT JOIN countries AS country ON country.id = partners.country_id

The database does all the heavy lifting, returning exactly one row per partner with all relations pre-aggregated.

Installation & Usage

Install via Composer:

composer require rgalstyan/laravel-aggregated-queries

Add the trait to your repository:

use Rgalstyan\LaravelAggregatedQueries\Traits\HasAggregatedQueries;

class YourRepository extends Repository
{
    use HasAggregatedQueries;
    
    public function getWithRelations()
    {
        return $this->aggregatedQuery()
            ->withJsonRelation('relation')
            ->withJsonCollection('collection')
            ->get();
    }
}

That's it! No configuration needed.

Database Support

✅ MySQL 8.0+
✅ PostgreSQL 12.0+
✅ Laravel 10, 11, 12
✅ PHP 8.2+

Why I Built This

I was working on a B2B platform connecting restaurants and travel agencies. The order listing API was generating 40+ queries and taking 300ms+ per request.

After implementing JSON aggregation, we reduced it to 1 query and 60ms - a 5x performance improvement that made the difference between a sluggish and snappy user experience.

I initially built this for Symfony (where the same problem exists with Doctrine), and the Laravel version followed based on community interest.

What's Next?

The package is MIT licensed and production-ready with:

Comprehensive test suite (PHPUnit)
PHPStan level 9 compliance
GitHub Actions CI/CD
Full documentation

Check it out:

📦 Packagist
🔗 GitHub

I'm actively maintaining it and open to feedback, issues, and PRs. If you're dealing with N+1 query problems in read-heavy scenarios, give it a try and let me know how it works for you!