从postgres中检索数组

Question

Very basic question but couldn´t find a solution.

In a table with some values I try to store in 1 column a array (int[]) and retrieve it. The storing and searching works fine but if I select it I get it as string in php.

Table week

col id (int) = 1
col days (int[]) = {1,1,1,1,1,0,0}

PHP

$query = SELECT id, days, manyother FROM week //array_to_json(days) does the same result
$pdo->setAttribute( PDO::ATTR_CASE, PDO::CASE_NATURAL );
$result = $pdo->query($query);

$test = $result->fetchAll(PDO::FETCH_ASSOC);
echo json_encode($test)

Returns:

id: 1
days: "[1,1,1,1,1,0,0]"
manyother: ""

I´m sure I miss just something with json_encode/decode Edit: its not affected by json_encode, I debuged it and before it returns the value like '[1,1,1,1,1,0,0]'.

Edit 2: Found a solution which works but increases the loading time 15* times :D So the question is still open but I have a workaround for the moment.

foreach($test as $key => $value){
        $test[$key]['days'] = json_decode($value['days']);
}

Answer 1

when selecting array, you can hack the way array is displayed:

t=# select json_build_array(array[1,2,3,4])->0;
 ?column?
-----------
 [1,2,3,4]
(1 row)

It should be easily evaluated by php then with eval. Or even json_agg on whole data set and then eval:

t=# select json_agg(s162) from s162;
json_agg
[{"i":0,"a":[1,2,3,4]},
 {"i":1,"a":[1,4]}]
(1 row)
Time: 0.281 ms