How to get a string array in SQL subqueries-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 this second subquery:

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

to return the category_table with all columns 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 which is not possible with SQL. 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

Where TO_ARRAY (which doesn't exist) is a SQL function that will convert the row into a string array.

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

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