Retrieve array from 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']);
}

Vao Tsun · Accepted Answer · 2017-05-19 10:41:23Z

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

edited May 19, 2017 at 10:41

answered May 19, 2017 at 9:54

Vao Tsun

52.4k13 gold badges114 silver badges149 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Doomenik Over a year ago

workdays:"[1,1,1,1,1,0,0]" still returns as string in response. I used Select json_build_array(days)->0 FROM week

Vao Tsun Over a year ago

sure. you still have to eval the string to get array

Doomenik Over a year ago

yes my question is how to do this. At the moment I get an string from postgress but I want an array.

Vao Tsun Over a year ago

probably by looping between rows and $day=eval($test[$i]["day"]);

Doomenik Over a year ago

Sry that I have t ask but could you explain it a little bit more clearly, I dont get it. The way you wrote it, it would return the whole table as array, but I just need the one column values converted.

|

Collectives™ on Stack Overflow

Retrieve array from postgres

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related