从postgres中检索数组
问题描述:
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']);
}
答
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