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

up vote
0
down vote

favorite

This is my database table

$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?

edited Nov 14 at 8:01

asked Nov 14 at 7:35

Roshan Jebin 01

2513

Possibly. But why are you storing your data in a non-relational format like this?
– ADyson
Nov 14 at 9:20

I am storing the data as json ecoded data
– Roshan Jebin 01
Nov 14 at 9:39

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
Nov 14 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.
– Roshan Jebin 01
Nov 14 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
Nov 14 at 11:20

|
show 1 more comment

up vote
0
down vote

favorite

This is my database table

$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?

edited Nov 14 at 8:01

asked Nov 14 at 7:35

Roshan Jebin 01

2513

Possibly. But why are you storing your data in a non-relational format like this?
– ADyson
Nov 14 at 9:20

I am storing the data as json ecoded data
– Roshan Jebin 01
Nov 14 at 9:39

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
Nov 14 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.
– Roshan Jebin 01
Nov 14 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
Nov 14 at 11:20

|
show 1 more comment

up vote
0
down vote

favorite

This is my database table

$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?

edited Nov 14 at 8:01

asked Nov 14 at 7:35

Roshan Jebin 01

2513

This is my database table

$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?

mysql arrays json laravel-5 mysqli

edited Nov 14 at 8:01

asked Nov 14 at 7:35

Roshan Jebin 01

2513

edited Nov 14 at 8:01

asked Nov 14 at 7:35

Roshan Jebin 01

2513

edited Nov 14 at 8:01

asked Nov 14 at 7:35

Roshan Jebin 01

2513

asked Nov 14 at 7:35

Roshan Jebin 01

2513

asked Nov 14 at 7:35

Roshan Jebin 01

2513

Possibly. But why are you storing your data in a non-relational format like this?
– ADyson
Nov 14 at 9:20

I am storing the data as json ecoded data
– Roshan Jebin 01
Nov 14 at 9:39

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
Nov 14 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.
– Roshan Jebin 01
Nov 14 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
Nov 14 at 11:20

|
show 1 more comment

Possibly. But why are you storing your data in a non-relational format like this?
– ADyson
Nov 14 at 9:20

I am storing the data as json ecoded data
– Roshan Jebin 01
Nov 14 at 9:39

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
Nov 14 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.
– Roshan Jebin 01
Nov 14 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
Nov 14 at 11:20

Possibly. But why are you storing your data in a non-relational format like this?
– ADyson
Nov 14 at 9:20

I am storing the data as json ecoded data
– Roshan Jebin 01
Nov 14 at 9:39

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
Nov 14 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.
– Roshan Jebin 01
Nov 14 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
Nov 14 at 11:20

|
show 1 more comment

1 Answer
1

active

oldest

votes

up vote
0
down vote

accepted

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 at 14:10

answered Nov 14 at 13:10

Jonas Staudenmeir

11.7k2932

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"
– Roshan Jebin 01
Nov 19 at 7:09

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
– Roshan Jebin 01
Nov 19 at 7:12

Anyways, thanks for your answer :) @Jonas Staudenmeir
– Roshan Jebin 01
Nov 19 at 7:12

add a comment |

Your Answer

StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");

StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});

function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});

}
});

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53295142%2fhow-to-get-the-value-of-specific-index-inside-an-array-column-using-mysql%23new-answer', 'question_page');
}
);

Post as a guest

Name

Required, but never shown

1 Answer
1

active

oldest

votes

1 Answer
1

active

oldest

votes

up vote
0
down vote

accepted

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 at 14:10

answered Nov 14 at 13:10

Jonas Staudenmeir

11.7k2932

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"
– Roshan Jebin 01
Nov 19 at 7:09

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
– Roshan Jebin 01
Nov 19 at 7:12

Anyways, thanks for your answer :) @Jonas Staudenmeir
– Roshan Jebin 01
Nov 19 at 7:12

add a comment |

up vote
0
down vote

accepted

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 at 14:10

answered Nov 14 at 13:10

Jonas Staudenmeir

11.7k2932

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"
– Roshan Jebin 01
Nov 19 at 7:09

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
– Roshan Jebin 01
Nov 19 at 7:12

Anyways, thanks for your answer :) @Jonas Staudenmeir
– Roshan Jebin 01
Nov 19 at 7:12

add a comment |

up vote
0
down vote

accepted

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 at 14:10

answered Nov 14 at 13:10

Jonas Staudenmeir

11.7k2932

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 at 14:10

answered Nov 14 at 13:10

Jonas Staudenmeir

11.7k2932

edited Nov 19 at 14:10

answered Nov 14 at 13:10

Jonas Staudenmeir

11.7k2932

answered Nov 14 at 13:10

Jonas Staudenmeir

11.7k2932

answered Nov 14 at 13:10

Jonas Staudenmeir

11.7k2932

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"
– Roshan Jebin 01
Nov 19 at 7:09

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
– Roshan Jebin 01
Nov 19 at 7:12

Anyways, thanks for your answer :) @Jonas Staudenmeir
– Roshan Jebin 01
Nov 19 at 7:12

add a comment |

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"
– Roshan Jebin 01
Nov 19 at 7:09

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
– Roshan Jebin 01
Nov 19 at 7:12

Anyways, thanks for your answer :) @Jonas Staudenmeir
– Roshan Jebin 01
Nov 19 at 7:12

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"
– Roshan Jebin 01
Nov 19 at 7:09

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
– Roshan Jebin 01
Nov 19 at 7:12

Anyways, thanks for your answer :) @Jonas Staudenmeir
– Roshan Jebin 01
Nov 19 at 7:12

add a comment |

draft saved

draft discarded

Thanks for contributing an answer to Stack Overflow!

Please be sure to answer the question. Provide details and share your research!

But avoid …

Asking for help, clarification, or responding to other answers.

Making statements based on opinion; back them up with references or personal experience.

To learn more, see our tips on writing great answers.

Some of your past answers have not been well-received, and you're in danger of being blocked from answering.

Please pay close attention to the following guidance:

Please be sure to answer the question. Provide details and share your research!

But avoid …

Asking for help, clarification, or responding to other answers.

Making statements based on opinion; back them up with references or personal experience.

To learn more, see our tips on writing great answers.

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Name

Required, but never shown

Name

Required, but never shown

This page is only for reference, If you need detailed information, please check here

搜尋此網誌

Nsryjdtyk