Laravel query where json array of objects contains

Question

I have a mysql table with a JSON column with data looking like that :

"[
{\"messageId\":\"0A0000000123ABCD1\",\"status\":\"request\\/0\",\"messagePrice\":\"0.03330000\"},            
{\"messageId\":\"0A0000000123ABCD2\",\"status\":\"request\\/0\",\"messagePrice\":\"0.03330000\"}
]"

How can I query the database to get the row containing a specific messageId (messageIds are uniques) ?

I tried :

DB::table('my_table')
 ->whereJsonContains('my_json_column',[['messageId'=>'0A0000000123ABCD1']]);

But I get no results.

Edit : I can make it work with a simple where like :

DB::table('notification_history')
    ->where('nexmo_responses','like','%0A0000000123ABCD1%')
    ->get();

But it's not really a clean way to do it.

your data looks like it's been double-encoded when it was inserted. That's probably the real root of your issue. — ADyson
– ADyson, Commented Aug 12, 2020 at 13:44
@ADyson Thanks you, didn't think about that. I was saving an associative array with json_encode, I just remove the json_encode and it's working perfectly. If you post your comment as an answer, I will accept it. — Alexis
– Alexis, Commented Aug 12, 2020 at 13:54

ADyson · Accepted Answer · 2020-08-12 13:56:50Z

1

Your data looks like it's been double-encoded when it was inserted. That's the root of your issue.

If you ensure the data is only encoded once, then it'll be readable correctly when you want to access it again.

answered Aug 12, 2020 at 13:56

ADyson

62.9k16 gold badges85 silver badges99 bronze badges

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

Collectives™ on Stack Overflow

Laravel query where json array of objects contains

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related