whereJsonContains with wildcard key named objects.

Hi all,

Imagine the following JSON object which is stored the column named extra.

{
	"XtJ3jcZzampkN8MN": {"id": 1},
	"jkoi1ejiodjo923d": {"id": 2}
}

I'm trying to use Eloquent to have a where clause on the id. I tried using it like ->whereJsonContains("extra->$", ['id', 1]) but that didn't work.

I feel like I'm really close but missing something in the syntax. Perhaps something like a wildcard? Thank you :)!

Tray2

4 years ago

Level 73

The question you should be asking yourself is, why are you using a json field in a relational database?

If you perform any kind of updates or filtering on the values inside the json string then extract it to a proper field. It will save you lot of headaches.

bluedog

4 years ago

Level 1

I'm aware of the decision to store JSON in a relational database.

With extract you're referring to JSON_EXTRACT?

kevinbui

4 years ago

Level 41

You might want to have a look at JSON_CONTAINS. Maybe something like this.

$id = 2;

Model::whereRaw("JSON_CONTAINS(extra, {$id}, '$.*.id')")->get();

bluedog

4 years ago

Level 1

Thanks, I'll try this out later :)

bluedog

4 years ago

Level 1

For some reason it can't parse the field 'extra' to JSON.

Invalid data type for JSON data in argument 1

I tried parsing it but unfortunately without any success. Thanks for pointing me in the right direction, I'll keep trying queries.

bluedog

4 years ago

Best Answer

Level 1

I decided to go for a "where like" query instead of trying to do a JSON search. Not the cleanest solution but I feel like I'm pushing the limits of JSON in a RDBMS here.

kingsleyuchenna

4 years ago

Level 3

Try this

->whereJsonContains('extra->XtJ3jcZzampkN8MN->id', 1);

Please or to participate in this conversation.