Where Query with Arrays for JSON Columns

[mathieutu]

Laravel Version

11.11.1

PHP Version

8.3.10

Database Driver & Version

PG13 (for the context, but not linked to the issue)

Description

There is an issue when using the query builder with JSON columns containing arrays. When trying to perform a query or even a unique validation, Laravel incorrectly handles the array by taking only the first value, leading to incorrect SQL generation and validation behavior.

 \App\Models\Place::where('coordinates', ['lat' => 0.1, 'lng' => 0.2])->toRawSql();

will output

select * from "places" where "coordinates" = 0.1

Instead of

select * from "places" where "coordinates" = '{"lat":0.1,"lng":0.2}'

This can be avoided in a where clause when we control the query, but it starts to be more difficult when using the unique validation rules and the array comes from the request.

This is coming from

framework/src/Illuminate/Database/Query/Builder.php

Line 892 in 05a9554

$this->addBinding($this->flattenValue($value), 'where');

and

framework/src/Illuminate/Database/Query/Builder.php

Lines 3944 to 3947 in 05a9554

	protected function flattenValue($value)
	{
	return is_array($value) ? head(Arr::flatten($value)) : $value;
	}

(In case of the first value is a number, it will fail silently, in case of a string, it will crash).

Steps To Reproduce

With a places table:

Schema::create('places', function (Blueprint $table) {
    $table->id();
    $table->json('coordinates');
    $table->timestamps();
});

namespace Tests\Feature;

use App\Models\Place;
use Illuminate\Foundation\Testing\RefreshDatabase;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\Route;
use Tests\TestCase;

class JsonColumnValidationTest extends TestCase
{
    use RefreshDatabase;

    public function test_it_handles_json_column_with_array_correctly()
    {
        $place = Place::create(['coordinates' => ['lat' => 0.1, 'lng' => 0.2]]);

        $placesCount = Place::where(['coordinates' => ['lat' => 0.1, 'lng' => 0.2]])->count();

        $this->assertEqual($placesCount, 1); // Fails!


        Route::post('/places', function (Request $request) {
            $request->validate(['coordinates' => 'required|unique:places,coordinates',]);
        });


        $response = $this->postJson('/places', [
            'coordinates' => ['lat' => 0.1, 'lng' => 0.2]
        ]);

        $response
            ->assertStatus(422) // Fails!
            ->assertJsonValidationErrors(['coordinates']);
    }
}

Available to create a PR with the changes we will discuss.

Thanks.

[DanteB918]

Hello, based on the Laravel 11 documentation for using where clauses with JSON columns, I don't believe this is the correct syntax. I think that you would need to use

\App\Models\Place::where('coordinates->lat', 0.1)->where('coordinates->lng', 0.2)->toRawSql();

[mathieutu]

I think that you would need to use (...)

This syntax works indeed, but this is not what this issue is about. We're talking here about querying with the whole json value. Symmetrically to how we create objects (and, including fixing the unique rule).

[driesvints]

Don't you need whereJsonContains here?

[mathieutu]

It would work. But whereJsonContains checks if an array/object contains the value. Not the equality of the value (and would produce a complicated query for nothing)

 \App\Models\Place::whereJsonContains('coordinates', ['lat' => 0.1, 'lng' => 0.2])->toRawSql();
 // select * from "places" where ("coordinates")::jsonb @> '{"lat":0.1,"lng":0.2}'

 \App\Models\Place::whereJsonContains('coordinates', ['lat' => 0.1])->exists(); // true but should be false in our case.

And we still have the issue with the unique validation rule, which uses where (probably among others).

@driesvints Do you want a PR with some failing tests?

[driesvints]

Right. I do think we've seen this in the past but determined it's hard to make this work with JSON arrays. Atm I can't find related issues but I'm sure there are some which were trying to do exactly what you're doing. Don't know where we landed at but thought we said we didn't support this use case.

[mathieutu]

(naive thoughts) What would prevent us to json encode the array instead of taking the first value?

[driesvints]

I think you can't just json encode it as the fields might have a different sequence and then a simple = comparison won't work.

[driesvints]

Going to close this one since we only received a single report. I feel there's workarounds here like separate columns for the latitude and longitude.