48

I have trouble reading Postgresql arrays in PHP. I have tried explode(), but this breaks arrays containing commas in strings, and str_getcsv() but it's also no good as PostgreSQL doesn't quote the Japanese strings.

Not working:

explode(',', trim($pgArray['key'], '{}'));
str_getcsv( trim($pgArray['key'], '{}') );

Example:

// print_r() on PostgreSQL returned data: Array ( [strings] => {または, "some string without a comma", "a string, with a comma"} )

// Output: Array ( [0] => または [1] => "some string without a comma" [2] => "a string [3] => with a comma" ) 
explode(',', trim($pgArray['strings'], '{}'));

// Output: Array ( [0] => [1] => some string without a comma [2] => a string, with a comma ) 
print_r(str_getcsv( trim($pgArray['strings'], '{}') ));
Improve this question
0

11 Answers 11

Reset to default
101

If you have PostgreSQL 9.2 you can do something like this:

SELECT array_to_json(pg_array_result) AS new_name FROM tbl1;

The result will return the array as JSON

Then on the php side issue:

$array = json_decode($returned_field);

You can also convert back. Here are the JSON functions page

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

Comments

18

As neither of these solutions work with multidimentional arrays, so I offer here my recursive solution that works with arrays of any complexity:

function pg_array_parse($s, $start = 0, &$end = null)
{
    if (empty($s) || $s[0] != '{') return null;
    $return = array();
    $string = false;
    $quote='';
    $len = strlen($s);
    $v = '';
    for ($i = $start + 1; $i < $len; $i++) {
        $ch = $s[$i];

        if (!$string && $ch == '}') {
            if ($v !== '' || !empty($return)) {
                $return[] = $v;
            }
            $end = $i;
            break;
        } elseif (!$string && $ch == '{') {
            $v = pg_array_parse($s, $i, $i);
        } elseif (!$string && $ch == ','){
            $return[] = $v;
            $v = '';
        } elseif (!$string && ($ch == '"' || $ch == "'")) {
            $string = true;
            $quote = $ch;
        } elseif ($string && $ch == $quote && $s[$i - 1] == "\\") {
            $v = substr($v, 0, -1) . $ch;
        } elseif ($string && $ch == $quote && $s[$i - 1] != "\\") {
            $string = false;
        } else {
            $v .= $ch;
        }
    }

    return $return;
}

I haven't tested it too much, but looks like it works. Here you have my tests with results: 

var_export(pg_array_parse('{1,2,3,4,5}'));echo "\n";
/*
array (
  0 => '1',
  1 => '2',
  2 => '3',
  3 => '4',
  4 => '5',
)
*/
var_export(pg_array_parse('{{1,2},{3,4},{5}}'));echo "\n";
/*
array (
  0 => 
  array (
    0 => '1',
    1 => '2',
  ),
  1 => 
  array (
    0 => '3',
    1 => '4',
  ),
  2 => 
  array (
    0 => '5',
  ),
)
*/
var_export(pg_array_parse('{dfasdf,"qw,,e{q\"we",\'qrer\'}'));echo "\n";
/*
array (
  0 => 'dfasdf',
  1 => 'qw,,e{q"we',
  2 => 'qrer',
)
*/
var_export(pg_array_parse('{,}'));echo "\n";
/*
array (
  0 => '',
  1 => '',
)
*/
var_export(pg_array_parse('{}'));echo "\n";
/*
array (
)
*/
var_export(pg_array_parse(null));echo "\n";
// NULL
var_export(pg_array_parse(''));echo "\n";
// NULL

P.S.: I know this is a very old post, but I couldn't find any solution for postgresql pre 9.2

Improve this answer

Comments

5

Reliable function to parse PostgreSQL (one-dimensional) array literal into PHP array, using regular expressions:

function pg_array_parse($literal)
{
    if ($literal == '') return;
    preg_match_all('/(?<=^\{|,)(([^,"{]*)|\s*"((?:[^"\\\\]|\\\\(?:.|[0-9]+|x[0-9a-f]+))*)"\s*)(,|(?<!^\{)(?=\}$))/i', $literal, $matches, PREG_SET_ORDER);
    $values = [];
    foreach ($matches as $match) {
        $values[] = $match[3] != '' ? stripcslashes($match[3]) : (strtolower($match[2]) == 'null' ? null : $match[2]);
    }
    return $values;
}

print_r(pg_array_parse('{blah,blah blah,123,,"blah \\"\\\\ ,{\100\x40\t\daő\ő",NULL}'));
// Array
// (
//     [0] => blah
//     [1] => blah blah
//     [2] => 123
//     [3] =>
//     [4] => blah "\ ,{@@ daőő
//     [5] =>
// )

var_dump(pg_array_parse('{,}'));
// array(2) {
//   [0] =>
//   string(0) ""
//   [1] =>
//   string(0) ""
// }

print_r(pg_array_parse('{}'));
var_dump(pg_array_parse(null));
var_dump(pg_array_parse(''));
// Array
// (
// )
// NULL
// NULL

print_r(pg_array_parse('{または, "some string without a comma", "a string, with a comma"}'));
// Array
// (
//     [0] => または
//     [1] => some string without a comma
//     [2] => a string, with a comma
// )
Improve this answer

Comments

5

If you can foresee what kind text data you can expect in this field, you can use array_to_string function. It's available in 9.1

E.g. I exactly know that my array field labes will never have symbol '\n'. So I convert array labes into string using function array_to_string

SELECT 
  ...
  array_to_string( labels, chr(10) ) as labes
FROM
  ...

Now I can split this string using PHP function explode:

$phpLabels = explode( $pgLabes, "\n" );

You can use any sequence of characters to separate elements of array.

SQL:

SELECT
  array_to_string( labels, '<--###DELIMITER###-->' ) as labes

PHP:

$phpLabels = explode( '<--###DELIMITER###-->', $pgLabes );
Improve this answer

2 Comments

in php should be $phpLabels = explode( '<--###DELIMITER###-->', $pgLabes );
Thanks @PawełMiłosz. Fixed the my answer.
5

As @Kelt mentioned:

Postgresql arrays look like this: {1,2,3,4}

You can just simply replace first { and last } with [ and ] respectively and then json_decode that.

But his solution works only for one-dimensional arrays.

Here the solution either for one-dimensional and multidimensional arrays:

$postgresArray = '{{1,2},{3,4}}';
$phpArray = json_decode(str_replace(['{', '}'], ['[', ']'], $postgresArray));  // [[1,2],[3,4]]

To cast back:

$phpArray=[[1,2],[3,4]];
$postgresArray=str_replace(['[', ']'], ['{', '}'], json_encode($phpArray));
Improve this answer

1 Comment

I didn't think about the multi-dimensional arrays. Very nice.
2

Based on the answers in the thread i created two simple php functions that can be of use:

private function pgArray_decode(string $pgArray){
    return explode(',', trim($pgArray, '{}'));
}

private function pgArray_encode(array $array){
    $jsonArray = json_encode($array, true);
    $jsonArray = str_replace('[','{',$jsonArray);
    $jsonArray = str_replace(']','}',$jsonArray);
    return $jsonArray;
}
Improve this answer

Comments

1

I tried the array_to_json answer, but unfortunalety this results in an unknown function error. Using the dbal query builder on a postgres 9.2 database with something like ->addSelect('array_agg(a.name) as account_name'), I got as result a string like { "name 1", "name 2", "name 3" }

There are only quotes around the array parts if they contain special characters like whitespace or punctuation.

So if there are quotes, I make the string a valid json string and then use the build-in parse json function. Otherwise I use explode.

$data = str_replace(array("\r\n", "\r", "\n"), "", trim($postgresArray,'{}'));
if (strpos($data, '"') === 0) {
    $data = '[' . $data . ']';
    $result = json_decode($data);
} else {
    $result = explode(',', $data);

}

Improve this answer

1 Comment

Did you used the PostgreSQL array_to_json() function?
1

If you have control of the query that's hitting the database, why don't you just use unnest() to get the results as rows instead of Postgres-arrays? From there, you can natively get a PHP-array.

$result = pg_query('SELECT unnest(myArrayColumn) FROM someTable;');
if ( $result === false ) {
    throw new Exception("Something went wrong.");
}
$array = pg_fetch_all($result);

This sidesteps the overhead and maintenance-issues you'd incur by trying to convert the array's string-representation yourself.

Improve this answer

Comments

0

I can see you are using explode(',', trim($pgArray, '{}'));

But explode is used to Split a string by string (and you are supplying it an array!!). something like ..

$string = "A string, with, commas";
$arr = explode(',', $string);

What are you trying to do with array? if you want to concatenate have a look on implode

OR not sure if it is possible for you to specify the delimiter other than a comma? array_to_string(anyarray, text)

Improve this answer

2 Comments

Sorry, the code wasn't very clear in my post. I've modified it so that second function argument is a string value and not an array. Note that PostgreSQL's array_to_string() isn't a solution to this case because it removes NULL and empty values which makes it impossible for me to iterate over arrays and link values from one array to their associated values in another array.
To avoid NULL problems, aproximate them to PHP "". Use array_to_string(anyarray, '","'), that is, SELECT '{"'|| array_to_string(anyarray, '","') || '"}' ... for PHP receive as string JSON $s and $newarray = json_decode($s);
0

Postgresql arrays look like this: {1,2,3,4}

You can just simply replace first { and last } with [ and ] respectively and then json_decode that.

$x = '{1,2,3,4}';
$y = json_decode('[' . substr($x, 1, -1) . ']');  // [1, 2, 3, 4]

To cast back the other way would be mirror opposite:

$y = [1, 2, 3, 4];
$x = '{' . substr(json_encode($y), 1, -1) . '}';
Improve this answer

1 Comment

If you have custom type in your array, it's not a valid JSON after suggested replace. E.g. {(xyz,123)} would become [(xyz,123)]
-1

A simple and fast function for converting deep PostgreSQL array string to JSON string without using pg connection.

function pgToArray(string $subject) : array
{
    if ($subject === '{}') {
       return array();
    }
    $matches = null;
    // find all elements; 
    // quoted: {"1{\"23\"},abc"} 
    // unquoted: {abc,123.5,TRUE,true} 
    // and empty elements {,,}
    preg_match_all( '/\"((?<=\\\\).|[^\"])*\"|[^,{}]+|(?={[,}])|(?=,[,}])/', $subject,$matches,PREG_OFFSET_CAPTURE);
    $subject = str_replace(["{","}"],["[","]"],$subject); // converting delimiters to JSON
    $matches = array_reverse($matches[0]);
    foreach ($matches as $match) {
        $item = trim($match[0]);
        $replace = null;
        if ((strpos($item,"{") !== false) || (strpos($item,"}") !== false)) {
            // restoring replaced '{' and '}' inside string
            $replace = $match[0];
         } elseif (in_array($item,["NULL","TRUE","FALSE"])) {
            $replace = strtolower($item);
         } elseif ($item === "" || ($item[0] !== '"' && !in_array($item,["null","true","false"]) && !is_float($item))) {
            $replace = '"' . $item . '"'; // adding quotes to string element
         }
         if ($replace) { // concatenate modified element instead of old element
            $subject = substr($subject, 0, $match[1]) . $replace . substr($subject, $match[1] + strlen($match[0]));
         }
     }
     return json_decode($subject, true);
  }
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.