Nette Query results in `string` instead of `array` for Postgres

[elbakerino]

Version: 3.1.2

Bug Description

I'm using Nette\Database to query Postgres DBs, using array_agg to group one column in an nested array, but instead it results in a string and not an already parsed array.

Steps To Reproduce

user_id	project_id
usr_189365	proj_012
usr_189365	proj_013

SELECT u.user_id, array_agg(u.project_id) as projects
FROM project_user u
GROUP BY user_id

PHP similiar to:

/** 
 * @var Nette\Database\Connection $db
 */ 

$res = $db->query(<<<SQL
SELECT u.user_id, array_agg(u.project_id) as projects
FROM project_user u
GROUP BY user_id
SQL
, $user_id);

$rows = $res->fetchAll();

Received:

$rows = [
    [
        'user_id' => 'usr_189365',
        'projects' => '{proj_012,proj_013}'
    ]
];

Expected Behavior

Returning an array like:

$rows = [
    [
        'user_id' => 'usr_189365',
        'projects' => [
            'proj_012',
            'proj_013',
        ],
    ]
];

Possible Solution

Do i need to activate/implement some serialization and/or can that be added somewhere? Didn't found a hint where to add that, so it automatically converts every array, no matter where I'm using fetchAll.

[milo]

Correct PostgreSQL array parsing is quite difficult. Parsing one dimension int or string is easy, but consider multidimension arrays of complex data types.

You can try SELECT u.user_id, json_agg(array_agg(u.project_id)) as projects. JSON decoding is quite easy in PHP.

[elbakerino]

Thanks, yes that works! But only one _agg is needed/supported:

SELECT u.user_id, json_agg(u.project_id) as projects