-
Notifications
You must be signed in to change notification settings - Fork 11k
New issue
Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.
By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.
Already on GitHub? Sign in to your account
Where Query with Arrays for JSON Columns #52459
Comments
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(); |
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). |
Don't you need |
It would work. But \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 @driesvints Do you want a PR with some failing tests? |
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. |
(naive thoughts) What would prevent us to json encode the array instead of taking the first value? |
I think you can't just json encode it as the fields might have a different sequence and then a simple |
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. |
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.will output
Instead of
This can be avoided in a
where
clause when we control the query, but it starts to be more difficult when using theunique
validation rules and the array comes from the request.This is coming from
framework/src/Illuminate/Database/Query/Builder.php
Line 892 in 05a9554
and
framework/src/Illuminate/Database/Query/Builder.php
Lines 3944 to 3947 in 05a9554
(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:
Available to create a PR with the changes we will discuss.
Thanks.
The text was updated successfully, but these errors were encountered: