Level 102
Oct 15, 2020
8
Level 1
JSON in Database, search for Entry by value in JSON?
Hey there. I have a Model with a field that contains JSON Data. Is there any way to search for an entry which has a specific value inside that JSON Object? So like ::where("something", "anotherValue")->first(); just that it searches for a key inside a JSON field instead of a field?
Level 1
Can I also use that on a Model?
User::whereJsonContains('connection_steam->id', $job->steam_id)->first();
Seems to not work. Whoops. Edit: This is for Arrays only... facepalm The one for Objects if above...
Level 75
Can I also use that on a Model?
See https://stackoverflow.com/questions/41942374/json-search-in-laravel-eloquent
Level 73
I highly suggest you don't store values in json in the database. It will most likely bite you in the ass later on.
You can use a json column to store data temporarily then let a worker extract it and store it in the proper columns in your table.
By not using json in your database model you will be able to harness the full power of you rdbms.
Level 1
I don't even know what "rdbms" is. In addition to that, if I would have it all in separate columns, I'd have 3 columns per Platform the User can connect their account with. If that's 20 Platforms, every User would have 60 Columns, just for Account Connections. And as I said, I just don't see why. The solution of @sinnbeck works perfectly fine for me and I don't have any problems.
Level 73
rdbms is Relational Database Management System in other words your database (MySQL, MariaDB, SQLite, Postgres, Oracle and so on).
It would give you 60 columns with a bad database model yes. In your case with a better database model it will give you a table something like this.
- id
- platform_id
- user_id
- key
- value
key = 'Some json key" value = "The key value" key = 'some other json key" value = "another key value"
Level 1
But that would make everything quite hard. Currently, I get the User and then just get the GitHub Connection Column from it. If I would do it like this, I would have to Find an entry in that Table where the Platform ID, User ID AND key is what I'm searching for...
Level 73
Yes and it will most likely be faster that using a like in you query
Please or to participate in this conversation.