Search code examples
phpmysqllaraveleloquentmysql-json

How to select rows containing a specific JSON column key, regardless of the value. All in Laravel and MySQL

I wrote this code in Laravel 10:

$unit = Unit::whereJsonContains('prefixes->abc', 'cba')->first();

And I have this query:

select * 
from `units` 
where json_contains(`prefixes`, '\"cba\"', '$."abc"') 
limit 1

I have a row in the database that contains { "abc": "cba", "cde": 100 } JSON in the 'prefixes' column.

My code works fine - it selects the correct row from the database. I want to select a row that contains a key named "abc" regardless of the value of that key. I can't achieve this.

I thought of making a selection like this:

select * 
from `units` 
where json_contains(`prefixes`, '%', '$."abc"') 
limit 1

but that doesn't work in phpadmin (it doesn't select anything).

I have not been able to get the right query through either Laravel Eloquent or raw SQL selection.

What should I do in Laravel and what should the SQL query look like?

Solution

  • JSON_CONTAINS_PATH would do the trick, e.g.

    select * from `units` where json_contains_path(`prefixes`, 'one', '$."abc"') limit 1