Breki
1 year ago

Test against value in array/JSON

Posted 1 year ago by Breki

I've got a database table called scrapeobjects which I use as temporary storage for a number of scraper and parser jobs. The structure is basically an id, a category text field, a type text field and a data JSON field (plus the created/updated_at fields, of course). Reading and writing to this from Eloquent has never been a problem and I've been able to use the flexibility of the JSON field to my advantage in many situations.

However, I've recently come across a situation where I need to ensure that an item in this table isn't already in the table, based on a key/value combination in the JSON field. I consider myself fairly good at Eloquent and figured it should be fairly simple, but no matter what I tried, it doesn't want to work.

Say we've just scraped the following data: {"id": 424, "caption": "European Championships France 2016", "league": "EC", "year": "2016", "currentMatchday": 3, "numberOfMatchdays": 7, "numberOfTeams": 24, "numberOfGames": 38, "lastUpdated": "2016-06-22T04:34:39Z"}. Now I want to send it to the parser, but due to the nature of the data coming into the system, we cannot be sure that another scraper hasn't added it already. So we wan to make sure that there's no data object with the same caption, for example.

So, how would I use Eloquent to use the model ScrapeObject, the column data and the field caption in a search? It's easy to ScrapeObject::first()->data->caption but I can't find a way to query the 'caption' field.

In the interrim, I'm using a primitive deserialization through in_array($this->option('caption'), ScrapeObject::pluck('data')->flatten()->unique()->toArray(), true)) ... which just upsets my feeling for good code standards, to be honest :D

Please sign in or create an account to participate in this conversation.