Add indexes for Postgres JSONB column type ordering with NULLs LAST

add_jsonb_sort_indexes.php

<?php

use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;

class AddDiffbotCompaniesIndexes extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        DB::statement("CREATE INDEX total_investment_desc_idx ON diffbot_companies USING btree ((diffbot_response -> 'totalInvestment'::text -> 'value'::text) desc nulls last);");
        DB::statement("CREATE INDEX total_investment_asc_idx ON diffbot_companies USING btree ((diffbot_response -> 'totalInvestment'::text -> 'value'::text) asc nulls last);");

        DB::statement("CREATE INDEX hq_location_desc_idx ON diffbot_companies USING btree ((diffbot_response -> 'location' -> 'city' -> 'name') desc nulls last);");
        DB::statement("CREATE INDEX hq_location_asc_idx ON diffbot_companies USING btree ((diffbot_response -> 'location' -> 'city' -> 'name') asc nulls last);");


        DB::statement("CREATE INDEX min_employees_desc_idx ON diffbot_companies USING btree ((diffbot_response -> 'nbEmployeesMin') desc nulls last);");
        DB::statement("CREATE INDEX min_employees_asc_idx ON diffbot_companies USING btree ((diffbot_response -> 'nbEmployeesMin') asc nulls last);");


        DB::statement("CREATE INDEX max_employees_desc_idx ON diffbot_companies USING btree ((diffbot_response -> 'nbEmployeesMax') desc nulls last);");
        DB::statement("CREATE INDEX max_employees_asc_idx ON diffbot_companies USING btree ((diffbot_response -> 'nbEmployeesMax') asc nulls last);");

        DB::statement("CREATE INDEX year_founded_desc_idx ON companies USING btree (year_founded desc nulls last);");
        DB::statement("CREATE INDEX year_founded_asc_idx ON companies USING btree (year_founded asc nulls last);");
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        DB::statement("DROP INDEX IF EXISTS total_investment_desc_idx;");
        DB::statement("DROP INDEX IF EXISTS total_investment_asc_idx;");

        DB::statement("DROP INDEX IF EXISTS hq_location_desc_idx;");
        DB::statement("DROP INDEX IF EXISTS hq_location_asc_idx;");

        DB::statement("DROP INDEX IF EXISTS min_employees_desc_idx;");
        DB::statement("DROP INDEX IF EXISTS min_employees_asc_idx;");

        DB::statement("DROP INDEX IF EXISTS max_employees_desc_idx;");
        DB::statement("DROP INDEX IF EXISTS max_employees_asc_idx;");

        DB::statement("DROP INDEX IF EXISTS year_founded_desc_idx;");
        DB::statement("DROP INDEX IF EXISTS year_founded_asc_idx;");
    }
}

AppServiceProvider.php

<?php

namespace App\Providers;

use Illuminate\Database\Eloquent\Builder;
use Illuminate\Routing\UrlGenerator;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\ServiceProvider;
use Illuminate\Support\Facades\Schema;

class AppServiceProvider extends ServiceProvider
{
    /**
     * Bootstrap any application services.
     *
     * @return void
     */
    public function boot(UrlGenerator $url)
    {
        // Can be used like: $query->orderByNullsLast('column_name', 'DESC');
        Builder::macro('orderByNullsLast', function($column, $direction = 'asc'){
            $column = $this->query->getGrammar()->wrap($column);
            $direction = strtolower($direction) === 'asc' ? 'asc' : 'desc';
            return $this->orderByRaw("$column $direction nulls last");
        });

    }
}