LARAVEL - where inside array have id and a value that greater than 80

Question

hello all so i have a question,

i have a sql database that have object array inside it, it will be like this

id_data	array	created_at
1	`{"id":1032,"prc":77},{"id":1033,"prc":97}`	2021-09-28 12:30:04
2	`{"id":1032,"prc":85},{"id":1034,"prc":97}`	2021-09-28 12:30:04
3	`{"id":1030,"prc":85},{"id":1031,"prc":97}`	2021-09-28 12:30:04
4	`{"id":1032,"prc":90},{"id":1033,"prc":97},{"id":1035,"prc":97}`	2021-09-28 12:30:04

and what i want to do is take every data with id 1032 that has prc greater than 80 with its

so in this table it would take id number 2, and 4

i need take the data using eloquent laravel, can somebody help me?, i'm still new to laravel

Data::where("something here")->get()

no it can't be like that, the id and prc inside the column array @HuzaifaQidwai — brian christian, Oct 11 '21 at 06:34
You can't do like this as your column array could be type of string. So in my point of view it's not possible by using eloquent. To use eloquent properly save this data in other table. — Yogendra, Oct 11 '21 at 07:09
yah i have that idea too, but i didnt have any permission to do it in my project now — brian christian, Oct 11 '21 at 07:39

Sina Kadkhodaei · Answer 1 · 2021-10-11T12:57:59.690

1

Take a see to the answer of a question like yours (How to query array inside JSON column in Eloquent).

You can use whereJsonContains (Laravel JSON queries).

This feature is not supported by the SQLite database

Data::whereJsonContains('array_field->id', '1032')->get();

Updated:

@brian christian: how i can get the "prc greater than"

...->where('array_field->prc','>', '80')

And mysql query is (Mysql JSON_TABLE):

select * from tbl where
   json_contains(array_field, '{"id":1032}') and
   (select min(all_prc.prc) from json_table(array_field,'$[*]."prc"' columns(prc int path '$')) as all_prc) > 80

After all you can use Regex like this:

select * from tbl where
   `array_field` REGEXP '\\"id\\":\\s{0,}1032' and
   `array_field` REGEXP '\\"prc\\":\\s{0,}(8[1-9]|9\\d)|(\\d{3,})'

In eloquent:

Data::whereRaw('`array_field` REGEXP \'\\"id\\":\\s{0,}1032\'')
    ->whereRaw('`array_field` REGEXP \'\\"prc\\":\\s{0,}(8[1-9]|9\\d)|([1-9]\\d{2,})\'')
    ->get();

\\"id\\":\\s{0,}1032: All rows that have id value 1032.

\\"prc\\":\\s{0,}([8-9]\\d)|([1-9]\\d{2,}): All rows that have prc greater than 80 (81-99) and (100-Infinite).

edited Oct 11 '21 at 12:57

answered Oct 11 '21 at 06:49

Sina Kadkhodaei

510
4
10

so, it literally we dont have to mantion column array ? – brian christian Oct 11 '21 at 06:54
In your question's table you mentioned that column name is `array`, so i wrote `array->id`. – Sina Kadkhodaei Oct 11 '21 at 06:59
i see, i think it will work, i see this link to https://stackoverflow.com/a/64860420/16706782 that you give to me. now i search how i can get the "prc greater than" – brian christian Oct 11 '21 at 07:37
Answer updated. – Sina Kadkhodaei Oct 11 '21 at 07:45
i cant use that, i have to combine that the data have id 1032 and also in id 1032 have a prc more than 80, this code literally give me the prc more than 80 but its no need to have the id 1032 on it. what i want to achieve is like this `Data::whereJsonContains('array', ['id' => 1032, 'prc' => 80])->get();` but if i use this code it give exact match 80 not more than 80 – brian christian Oct 11 '21 at 08:15
You should write these methods together: `Data::whereJsonContains('array->id', '1032')->where('array->prc','>', '80')->get();` – Sina Kadkhodaei Oct 11 '21 at 08:32

LARAVEL - where inside array have id and a value that greater than 80

1 Answers1