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
– polishboy14f4, Commented Aug 13, 2019 at 6:14

Ajinkya Jadhav · Accepted Answer · 2019-08-13 06:08:16Z

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, 2019 at 6:08

Ajinkya Jadhav

113 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

polishboy14f4 Over a year ago

forHow can I do that in Laravel for query building?

Mohamad Karimisalim · Accepted Answer · 2019-08-13 07:11:58Z

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, 2019 at 7:11

answered Aug 13, 2019 at 7:06

Mohamad Karimisalim

1702 silver badges9 bronze badges

1 Comment

Mohamad Karimisalim Over a year ago

sorry for the delay, but in case you still stuck with this problem, I think further link help you stackoverflow.com/questions/48016520/…

Collectives™ on Stack Overflow

Find element in array from column in MySQL

2 Answers 2

1 Comment

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related