Be part of JetBrains PHPverse 2026 on June 9 – a free online event bringing PHP devs worldwide together.

phayes0289's avatar

How to query a JSON string inside of Eloquent

I have a user preference table that looks like this:

 public function up()
    {
        Schema::create('user_preferences', function (Blueprint $table) {
            $table->id();
            $table->unsignedBigInteger('user_id');
            $table->string('key');
            $table->text('value');
            $table->timestamps();

            $table->foreign('user_id')->references('id')->on('users')->onDelete('cascade');
            $table->unique(['user_id', 'key']);
        });
    }

In there I store various user specific preferences including:

sms_preference: a boolean equal to 1 or 0 and sms_options: a string field that stores the values of various checkboxes submitted from a form. The database field for any given user may look different as they may chose difference checkboxes, like the following example:

["EAM"]

[Supervisory","Routine"]

["EAM","Routine"]

etc.

I have an observer that looks for a new database entry. When there is one, a query is run to see who should get notified. That eloquent query looks like this:

$usersToNotify = User::whereHas('preferences', function ($query) {
            $query->where('key', 'sms_preference')->where('value', '1');
        })
            ->whereHas('userAlerts', function ($query) use ($incidentType) {
                $query->where('notifier_id', $incidentType);
                $query->whereColumn('user_alerts.duty_status', 'users.duty_status');
            })
            ->get();

I need to add an “and” and query the “preferences” relationship again, this time looking for the string “EAM” inside the key “sms_options”. It needs to be able to include records that look like the following:

["EAM"]

["EAM","Routine"]

but not,

[Supervisory","Routine"]

I was thinking of adding something like the following, which I know does not work:

''' ->whereJsonContains('key', 'sms_options')->where('value', 'EAM'); '''

So How do I accomplish this?**

0 likes
2 replies
LaryAI's avatar
LaryAI
AI
Best Answer
Level 58

To accomplish the task of querying a JSON string inside of an Eloquent relationship, you can use the whereJsonContains method provided by Laravel. This method allows you to query JSON column data types to find records where the JSON column contains the specified value.

Here's how you can modify your existing query to include the check for the "EAM" string inside the "sms_options" key:

$usersToNotify = User::whereHas('preferences', function ($query) {
    $query->where('key', 'sms_preference')->where('value', '1');
})
->whereHas('preferences', function ($query) {
    $query->where('key', 'sms_options')->whereJsonContains('value', 'EAM');
})
->whereHas('userAlerts', function ($query) use ($incidentType) {
    $query->where('notifier_id', $incidentType);
    $query->whereColumn('user_alerts.duty_status', 'users.duty_status');
})
->get();

In this query, we've added another whereHas clause to filter the users based on their preferences. The whereJsonContains method is used to check if the 'value' column (which contains JSON data) has an element with the value "EAM".

Please note that the whereJsonContains method is only available for MySQL 5.7+ and PostgreSQL 9.4+, and the column type must be a valid JSON column type in your database.

Also, ensure that the 'preferences' relationship is correctly defined in your User model to reference the user_preferences table. Here's an example of what the relationship might look like:

public function preferences()
{
    return $this->hasMany(UserPreference::class);
}

Replace UserPreference with the actual name of your Eloquent model for the user_preferences table.

Please or to participate in this conversation.