Follow

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use
Contact

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 got 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 to make a selection like:

MEDevel.com: Open-source for Healthcare and Education

Collecting and validating open-source software for healthcare, education, enterprise, development, medical imaging, medical records, and digital pathology.

Visit Medevel

`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 querry look like?

>Solution :

JSON_CONTAINS_PATH would do the trick, e.g.

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

Leave a Reply

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use

Discover more from Dev solutions

Subscribe now to keep reading and get access to the full archive.

Continue reading