Find element in array from column in MySQL

Question

I have a table "Notifications". In my data column, I have an array-like:

{  
   "title":"Something",
   "arr":{  
      "id":12,
      "name":"HelloWorld",
      "avatar":null
   }
}

And I want to check element (notify) which arr->id == 12 for example (or something else). I tryed make query buildng but nothing works. Examples:

$hello = auth()->user()->unreadNotifications->where('notifiable_id', Auth::user()->id)
                             ->where('data', '%12%')->first();

or

$hello = auth()->user()->unreadNotifications->where('notifiable_id', Auth::user()->id)
                              ->where('data->arr->id', '12')->first();

Who knows how do that?

Example of data column you have in first code block. If you think about type of column, it's a text — polishboy14f4, Aug 13 '19 at 06:14

score 0 · Answer 1 · answered Aug 13 '19 at 06:08

0

You can use this ..SELECT JSON_SEARCH(......) for more reference you can refer this link How to search JSON array in MySQL?

answered Aug 13 '19 at 06:08

Ajinkya Jadhav

11
3

forHow can I do that in Laravel for query building? – polishboy14f4 Aug 13 '19 at 06:17

Mohamad Karimisalim · Answer 2 · 2019-08-13T07:11:58.633

0

I think this would help:

$arr = 'arr';
$id = 'id';
$hello = auth()->user()->unreadNotifications->where('notifiable_id', Auth::user()->id)->where("data->{$arr}->{$id}", '12')->first();

edited Aug 13 '19 at 07:11

answered Aug 13 '19 at 07:06

Mohamad Karimisalim

130
2
9

sorry for the delay, but in case you still stuck with this problem, I think further link help you https://stackoverflow.com/questions/48016520/making-a-laravel-5-4-query-on-a-json-field-containing-a-json-array – Mohamad Karimisalim Aug 14 '19 at 14:27

Find element in array from column in MySQL

2 Answers2