Doing a MySQL query to JSON fields case-insensitively using Laravel

If you’re stuck and wondering if there’s a way to query for a JSON “metadata” in your database case-insensitively, there you go!

public function scopePropertyLike(Builder $query, string $keyword)
{
    $query->whereRaw(
        'LOWER(documents.metadata->"$.property.fullAddress") like ?',
        '%'. strtolower($keyword) . '%'
    );
}

As you see, you need to use a raw query to make it works. Please be aware of SQL injection whenever you will use raw queries. Make sure you always write them using variable bindings.

Alright. If you’re now wondering what the heck is $ there, it’s a part of a JSONPath notation. So documents is the table, metadata is the column, and "$.property.fullAddress" is the JSONPath expression of the data to query.

In this case, this is how the data looks like.

{
  "property": {
    "id": 12345,
    "fullAddress": "Jalan Malioboro, Yogyakarta",
    "province": "Daerah Istimewa Yogyakarta"
  }
}

You may want to find more references about JSONPath on JSONPath.com.

Hope it helps.

---

---

---

---