0

UPDATE: WORKING SOLUTIONS

Getting an array type:

column1, CONCAT('[', GROUP_CONCAT(column2), ']') AS array FROM table GROUP BY column1

And this returns a column named array with following results: [x,y,z]

Getting an object type:

column1, CONCAT('[', GROUP_CONCAT(CONCAT('{ property_A:', column2, ', property_B:', column3, '}')), ']') AS object FROM table GROUP BY column1

And this returns a column named object with following results: [{property_A: x, property_B: z},{property_A: y, property_B: w}]

ORIGINAL QUESTION

I have SQL table like that:

N | B
1 | z
1 | w
1 | y
2 | x
2 | k

how has to be the query that returns something like that:

N | C => alias of collection of B
1 | [z, w, y]
2 | [x, k]

Thanks

Ale
  • 117
  • 1
  • 13

1 Answers1

1

You do not "return arrays" in SQL, you return datasets or sets of tuples. So you would request,

SELECT * FROM `table` WHERE N = 1;

and it would return

z
w
y

In MySQL there exists the GROUP_CONCAT function so that

SELECT GROUP_CONCAT(value) FROM `table`

would return

'z,w,y'

and, from MySQL 5.7.22+, you can actually return an array (rather, a JSON array) using

 SELECT JSON_ARRAYAGG(value) FROM `table` WHERE N=1;

 ["z", "w", "y"]

Ordinarily you would just cycle the dataset and get your array. This is actually possible with ready-made functions in many languages, and since you mentioned PHP, it is very easy to do it using PDO:

// This is the first column, so it has the number 0.
$stmt = $pdo->prepare('SELECT value FROM `table` WHERE N = :n');
$stmt->execute([ ':n' => 1 ]);
$arr  = $stmt->fetchAll(PDO::FETCH_COLUMN, 0);

Now $arr is a PHP array: [ 'z', 'w', 'y' ].

LSerni
  • 55,617
  • 10
  • 65
  • 107
  • Yes, it's not exactly what I wanted, but it's good enough. I can parse the result in PHP once I've fetched the data. Thank you. – Ale Dec 10 '20 at 21:38
  • Um, wait. You wanted a PHP array? Amending answer... – LSerni Dec 10 '20 at 22:11
  • Actually it seems that MariaDB doesn't have JSON_ARRAYAGG, so I found a good alternative with this script: SELECT column1, CONCAT('[', GROUP_CONCAT(column2), ']') AS column2 FROM table GROUP BY column1; and this one does the job perfectly. Thank you! – Ale Dec 11 '20 at 23:56
  • One more update: in case you need to create an object style, this is the script I've found: SELECT c1, CONCAT('[', GROUP_CONCAT(CONCAT('{ c2:', c2, ', c3:', c3, '}')), ']') AS object FROM table GROUP BY c1 – Ale Jan 08 '21 at 12:24