You may use whereJsonContains to query JSON arrays. This feature is not supported by the SQLite database. JSON Where Clauses
I have a relationship using whereJsonContains. Upon runing unit test with in-memory sqlite, I receive this error
This database engine does not support JSON contains operations.
Edit: The whereJsonContains() condiion is applied on the notifications table data column. The toArray() method in the notification class is adding json format by default.
You have two options
I would go with option 1. seven days a week, since a storing json in your database like that defeats the whole purpose of a relational database.
I have updated my question explaining that it's the notifications table where the toArray() method is responsible for adding json format.
+1 for relaton model. if you need json structure in db with indexes, filtering etc, use nosql like mongodb etc
In my practice with mysql, there is no success story with json type. if I doubt the need to pivot tables but expect a different structure from the user, I make the field as text and casts it in the appropriate model.
in your case, you are sure that you store the data in the json format, not the plain text?
Just posting this in case anyone comes across this post and is interested in using whereJsonContains and not blowing out their sqlite testing configuration.
You can specify a custom grammar for the connection and add support for JSON_CONTAINS method like so:
class PatchedSQLiteGrammar extends SQLiteGrammar
{
public function __construct()
{
DB::connection()->getPdo()->sqliteCreateFunction('JSON_CONTAINS', function ($json, $val, $path = null) {
$array = json_decode($json, true, 512, JSON_THROW_ON_ERROR);
$val = trim($val, '"');
if ($path) {
return $array[$path] == $val;
}
return in_array($val, $array, true);
});
}
protected function compileJsonContains($column, $value)
{
[$field, $path] = $this->wrapJsonFieldAndPath($column);
return 'json_contains('.$field.', '.$value.$path.')';
}
}
And in the setup method of your test suite, you can swap the grammar out:
app('db.connection')->setQueryGrammar(new PatchedSQLiteGrammar());
Hi ! @campo This code save my live. I also had some ocurrences of the mysql CONCAT function in the code base I am testing. How I can extend you answer to include support for this? I know this function is not supported in sqlite but uses || instead. Thanks in advance!
@campo I had to change it to support searching within json list of arrays, but you pointed me to right direction. Many thanks.
DB::connection()->getPdo()->sqliteCreateFunction('JSON_CONTAINS', function ($json, $val, $path = null) {
try {
$jsonData = json_decode($json, true, 512, JSON_THROW_ON_ERROR);
$valData = json_decode($val, true, 512, JSON_THROW_ON_ERROR);
if ($path) {
// Navigate to the specified path
$path = ltrim($path, '$.');
$keys = explode('.', $path);
foreach ($keys as $key) {
if (isset($jsonData[$key])) {
$jsonData = $jsonData[$key];
} else {
return 0; // Path doesn't exist
}
}
}
// If $jsonData is an array, check if it contains the $valData
if (is_array($jsonData)) {
// Handle the case where $jsonData is an array of objects
foreach ($jsonData as $item) {
if (is_array($item) && $item == $valData) {
return 1; // Found a matching object
}
}
// Handle the case where $jsonData is a simple array
if (in_array($valData, $jsonData, true)) {
return 1; // Found a matching value
}
} else {
// If $jsonData is a single value, compare directly
if ($jsonData == $valData) {
return 1; // Values match
}
}
return 0; // No match found
} catch (JsonException $e) {
return 0; // JSON decoding error
}
});
Please or to participate in this conversation.