README

A Laravel package that provides fluent, database-agnostic JSON querying macros for Eloquent and Query Builder. Seamlessly work with JSON columns across MySQL, PostgreSQL, and SQLite with a consistent, expressive API.

Features

• 🔍 Database Agnostic: Works seamlessly with MySQL, PostgreSQL, and SQLite
• 🎯 Fluent API: Clean, expressive syntax for JSON queries
• 🚀 Multiple Macros: Filter, select, order, search, and check existence
• 🔧 Easy Integration: Auto-discovery service provider
• 🧪 Well Tested: Comprehensive test suite
• 📦 Laravel Compatible: Supports Laravel 9.x, 10.x, 11.x, and 12.x

Installation

You can install the package via Composer:

composer require pawsmedz/laravel-json-filter

The package will automatically register its service provider via Laravel's auto-discovery feature.

Usage

Basic JSON Filtering

Filter records based on JSON field values:

use App\Models\User;

// Filter by JSON field value
$activeUsers = User::query()
    ->jsonFilter('meta->status', '=', 'active')
    ->get();

// Filter nested JSON paths
$proUsers = User::query()
    ->jsonFilter('meta->subscription->plan', '=', 'pro')
    ->get();

JSON Where In

Filter records where JSON field matches any value in an array:

// Find users with specific statuses
$users = User::query()
    ->jsonWhereIn('meta->status', ['active', 'pending'])
    ->get();

// Multiple subscription plans
$subscribers = User::query()
    ->jsonWhereIn('meta->subscription->plan', ['pro', 'enterprise'])
    ->get();

JSON Contains (Search)

Search for records where JSON field contains a specific substring:

// Search in JSON arrays or strings
$developers = User::query()
    ->jsonContains('meta->skills', 'php')
    ->get();

// Search in nested fields
$users = User::query()
    ->jsonContains('meta->profile->bio', 'developer')
    ->get();

JSON Exists

Check if a JSON path exists in records:

// Find users with subscription data
$subscribers = User::query()
    ->jsonExists('meta->subscription->plan')
    ->get();

// Check for nested paths
$profileUsers = User::query()
    ->jsonExists('meta->profile->preferences')
    ->get();

JSON Select

Select specific JSON fields as columns:

// Select JSON field with auto-generated alias
$users = User::query()
    ->jsonSelect('meta->status')
    ->get();

// Select with custom alias
$users = User::query()
    ->jsonSelect('meta->profile->country as user_country')
    ->get();

// Multiple JSON selections
$users = User::query()
    ->jsonSelect('meta->status')
    ->jsonSelect('meta->subscription->plan as plan')
    ->jsonSelect('meta->profile->country as country')
    ->get();

JSON Order By

Order results by JSON field values:

// Order by JSON field (ascending)
$users = User::query()
    ->jsonOrderBy('meta->score')
    ->get();

// Order by JSON field (descending)
$topUsers = User::query()
    ->jsonOrderBy('meta->score', 'desc')
    ->get();

// Complex ordering
$users = User::query()
    ->jsonOrderBy('meta->subscription->plan', 'desc')
    ->jsonOrderBy('meta->score', 'desc')
    ->get();

Combining Multiple Macros

Chain multiple JSON operations for complex queries:

$results = User::query()
    ->jsonFilter('meta->status', '=', 'active')
    ->jsonExists('meta->subscription')
    ->jsonWhereIn('meta->subscription->plan', ['pro', 'enterprise'])
    ->jsonContains('meta->skills', 'laravel')
    ->jsonSelect('meta->profile->name as display_name')
    ->jsonSelect('meta->subscription->plan as plan')
    ->jsonOrderBy('meta->score', 'desc')
    ->get();

Database Support

The package automatically detects your database driver and uses the appropriate JSON syntax:

MySQL

-- jsonFilter('meta->status', '=', 'active')
WHERE JSON_UNQUOTE(JSON_EXTRACT(meta, '$.status')) = 'active'

-- jsonSelect('meta->status as user_status')
SELECT JSON_UNQUOTE(JSON_EXTRACT(meta, '$.status')) as user_status

PostgreSQL

-- jsonFilter('meta->status', '=', 'active')
WHERE meta->>'status' = 'active'

-- jsonSelect('meta->status as user_status')
SELECT meta->>'status' as user_status

SQLite

Falls back to basic column operations for compatibility.

Requirements

• PHP 8.1 or higher
• Laravel 9.x, 10.x, 11.x, or 12.x
• MySQL, PostgreSQL, or SQLite database

Testing

Run the test suite:

composer test

Run tests with coverage:

composer test:coverage

Contributing

Please see CONTRIBUTING for details.

Security Vulnerabilities

Please review our security policy on how to report security vulnerabilities.

Credits

• Rayvand Jasper Valle Medrano
• All Contributors

License

The MIT License (MIT). Please see License File for more information.