CODEWITHSUNDEEP
phpmysql
Brad
Brad

Reputation: 12262

return group_concat data as array

I want to return values I retrieve from the db using group_concat as an array of data. Is it possible to do this in the mysql query? Or do I need to explode the data into an array?

GROUP_CONCAT(sh.hold_id) as holds

returns this 

[holds] => 3,4

I want it to return:

[holds] => array(3,4)

Upvotes: 26

Views: 31856

Answers (6)

Melvin Justine Bayogo
Melvin Justine Bayogo

Reputation: 1

CONCAT('["', GROUP_CONCAT(sh.hold_id separator '","'), '"]') as holds

will return

["val1","val2"]

Upvotes: 0

Bonttimo
Bonttimo

Reputation: 671

This is possible since MySQL 5.7.22 using the JSON_ARRAYAGG() method

Read more: https://dev.mysql.com/doc/refman/5.7/en/aggregate-functions.html#function_json-arrayagg

Example:

SELECT JSON_ARRAYAGG(category.slug) as categories From categories

Upvotes: 9

Uttam
Uttam

Reputation: 596

It is possible to return mysql JSON array like so,

json_array(GROUP_CONCAT(sh.hold_id)) as holds

Refer docs for further info.

Upvotes: 1

Matteo Tassinari
Matteo Tassinari

Reputation: 18584

As I said in my comment: you need to explode the data into an array, using php code like this:

$holds = explode(',', $holds);

because mysql has no concept of array-type for data.

Upvotes: 21

Hasan Mhd Amin
Hasan Mhd Amin

Reputation: 407

If you need to do it on the MySQL level, and then you may probably parse it to an object. You can do the following

SELECT CONCAT("[", GROUP_CONCAT(category.name), "]") AS categories
From categories

Upvotes: 5

arkascha
arkascha

Reputation: 42885

MySQL has no concept of arrays. Therefore it is not able to return an array. It is up to your processing code (here the php scripts) to convert the concatenated notation into a php array.

Upvotes: 4

Related Questions