How to get the value of specific index inside an array column using mysql?

Question

$reports = DB::table('attendance_report_details')
            ->select('*','project_hours[$index_id]')
            ->get();

I am passing index id to the function. Is there a way to do it like this?

Possibly. But why are you storing your data in a non-relational format like this? — ADyson
– ADyson, Commented Nov 14, 2018 at 9:20
I know you are, I can see that. My question was why are you doing it like that, rather than creating a proper relational structure? — ADyson
– ADyson, Commented Nov 14, 2018 at 10:40
Because the project hour is related to a corresponding project.The sequence of project may differ for each att_rep_id.Also, old projects may be deleted. So its needed to store it like this. — Rosh
– Rosh, Commented Nov 14, 2018 at 11:16
None of that stops you from making a proper structure using separate tables and foreign keys. If you did that you could have many-to-many relationships linking the employee table to the project table via a project hours table which stores the hours, and foreign keys to the employee and projects tables. Then writing queries to get particular hours is trival using SQL. Maybe you first need to study database design in more detail if you didn't realise this kind of thing? — ADyson
– ADyson, Commented Nov 14, 2018 at 11:20

Jonas Staudenmeir · Accepted Answer · 2018-11-19 14:10:24Z

1

Use the arrow operator (MySQL-only):

$reports = DB::table('attendance_report_details')
    ->select('*', DB::raw("project_hours->'$[".(int) $index_id."]'"))
    ->get();

Or its alias JSON_EXTRACT() (MySQL & MariaDB):

$reports = DB::table('attendance_report_details')
    ->select('*', DB::raw("json_extract(project_hours, '$[".(int) $index_id."]')"))
    ->get();

edited Nov 19, 2018 at 14:10

answered Nov 14, 2018 at 13:10

Jonas Staudenmeir

26k7 gold badges70 silver badges114 bronze badges

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

3 Comments

Rosh Over a year ago

Nope.. :( I am getting an error. For the first solution the error is "Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '>'$[1]' from attendance_report_details' at line 1"

Rosh Over a year ago

For the second solution: json functions can be used only in mariaDB version 10.2.3 and above . My version is 10.1.36. I am using xampp

Rosh Over a year ago

Anyways, thanks for your answer :) @Jonas Staudenmeir

Collectives™ on Stack Overflow

How to get the value of specific index inside an array column using mysql?

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related