How to get a string array in sql subquerries-CodePudding

I'm looking for a SQL query that returns multiple data from different table. Here is an example:

SELECT 
product.*,
(SELECT category_name FROM category WHERE product.category_id = category_id) AS category_name
FROM product_table AS product
WHERE product.id = 17

This request will work perfectly BUT i need that the second subquery =>

(SELECT category_name FROM category WHERE product.category_id = category_id) AS category_name

Returns the category_table with alll collumns converted into an string-array. So i can explode(',', $category_name) in PHP.

So in other words i'm trying to retrieve a table inside another table wich is not possible with SQL. So i want to convert the row in the category_table into a string-array. And then convert it back to a real array in PHP.

Expected result:

ProductObject {
id => 1,
name => "Product Name",
category => "[id, category_name, category_description]" # It also be a json
}

Expected solution type:

(SELECT TO_STRING_ARRAY(*) FROM category WHERE product.category_id = category_id) AS category_table

Which TO_ARRAY (doesn't exists) is a SQL function that will convert the row into a string array.

Also this could be a very usefull solution cause we can with this get tables inside tables (nested tables) in only one SQL query.

Thanks in advance !

CodePudding user response：

Use CONCAT separator, but there will be problem if your column values contain the separator

SELECT a.*, CONCAT(b.category_id, ',', b.category_name, ',', b.category_description) AS cat_info
FROM product_table a 
LEFT JOIN category b ON a.category_id = b.category_id
WHERE a.id = 17

Or use JSON_ARRAY

SELECT a.*, JSON_ARRAY(b.category_id, b.category_name, b.category_description) AS cat_info
FROM product_table a 
LEFT JOIN category b ON a.category_id = b.category_id
WHERE a.id = 17

CodePudding user response：

AS A PARTIAL SOLUTION THAT WORKS GREAT THO but could be improved to automatically get each columns names as key

Thanks to @progu i've done some search with JSON_ARRAY and found JSON_OBJECT that fits better to my needs.

Partial solution:

SELECT 
 p.*, 
 (SELECT JSON_OBJECT(
     "id", category.id,
     "name", category.name,
     "description", category.description
 ) FROM category WHERE p.category_id = category.id ) AS _category,
FROM product_table p
WHERE p.id = 17

JSON_OBJECT works well in my case because i needed the 'key' too. JSON_ARRAY can work in some case but i'm using function to build Object in PHP from a json string (keys are important for the right object property recognition).

So with JSON_OBJECT i can symply do a json_decode and i have an object.

Is there a way to get each columns names automatically ?

So i wouldn't need to type by hand all the time this:

JSON_OBJECT(
    THIS => "id", 
        category.id,
    THIS => "name", 
        category.name,
    THIS => "description", 
        category.description
)

As JSON_OBJECT('key_1', 'value_of_key_1', 'key_2', 'value_of_key_2') works that way.

Thanks guys