Convert postgresql hstore to php array

I believe the syntax would be something like this:

$pdo = new PDO( /*connection string*/ );
// h is the hstore column.
$stmt = $pdo->query( "SELECT (each(h)).key, (each(h)).value FROM <table name>" );
$output = array();
foreach( $stmt->fetchAll( PDO::FETCH_NUM ) as $row )
{
   // $row[ 0 ] is the key, $row[ 1 ] is the value.
   $output[ $row[ 0 ] ] = $row[ 1 ];
}

Just in case somebody is still interested in this: since 9.2 there is a function called hstore_to_json that can be used in select clauses to convert the hstore content to JSON. This will look like this:

SELECT id, hstore_to_json(my_hstore_field) AS myField FROM mytable WHERE ...

Then in PHP just use

json_decode($row['myField'])

to decode it to a php array...

$hstore = '"A"=>"AAA", "B"=>"BBB"';
print_r(json_decode('{' . str_replace('"=>"', '":"', $hstore) . '}', true));

I attempted to make use of Pomm's PgHStore method, however it broke under a half dozen or so different circumstances. I don't remember them all, but here are a few I do recall:

• Lack of native PHP Null support
• Lack of properly escaping double quotes
• Didn't properly escape values for safe PostgreSQL insertion

I ultimately ended up deriving my own solution, coined PHPG. Supports automatic conversion of arrays of any data-type, Hstores, Geometric data-types, Dates / Timestamps, etc: https://github.com/JDBurnZ/PHPG

If you wish to tackle this problem without the use of an external class or library, this is going to be very difficult and will most likely be reliable, and here's why:

One might suggest you could simply explode on "," to create a list of key/value pairs, from which you could explode each element in the list on "=>". This works if all values are strings, but PostgreSQL Hstores support NULL values. NULL values are not encapsulated in double quotes, so the above solution will not work appropriately in these scenarios.

Since none of the other solutions seem to be working perfectly, I ended up with a rather conservative two-part approach:

protected function toPostgresHStoreLiteral(array $array) {
    return join(',', F\map($array, function ($value, $key) {
        switch (strtolower(gettype($value))) {
            case 'null' :
            case 'boolean' :
                $value = $value ? 'true' : '';
                break;

            case 'object' :
                if (!is_callable([$value, '__toString'])) {
                    throw new \InvalidArgumentException(sprintf('Cannot cast object of type %s to string', get_class($value)));
                }
                // deliberate fallthrough
            case 'integer' :
            case 'double' :
            case 'string' :
                settype($value, 'string');
                break;

            default :
                throw new \InvalidArgumentException(sprintf('Cannot store values of type %s in an hstore', gettype($value)));
        }

        return call_user_func_array('sprintf', array_reduce(
            [$key, $value],
            function (array $args, $value) { return array_merge($args, [sprintf('"%s"', addcslashes($value, '"\\'))]); },
            ['%s=>%s']
        ));
    }));
}

This method formats an array into an hstore literal string ready to be inserted into a query. It may be a little more functional in style than necessary, sorry for that. ^_^;; Depends on PHP 5.4+ and functional-php.

To get hstore values out of Postgres, I'm using JSON as the middle man:

SELECT array_to_json(hstore_to_array(value)) AS value ...

This gets a JSON encoded array which can be turned into a regular PHP array using this:

protected function postgresJsonHstoreToArray($json) {
    $values = json_decode($json, true);
    $array  = [];

    for ($i = 0, $length = count($values); $i < $length; $i++) {
        $key   = $values[$i];
        $value = $values[++$i];
        $array[$key] = $value;
    }

    return $array;
}

This depends on Postgres 9.2+ or 9.1 with the json_91 backport.

This is copied straight out of some custom code, not necessarily very generalized, but it has withstood some realworld usage already.

I had the same problem myself, so I came up with the following solution:

function hstore2array($data) {
    preg_match_all('/(?:"((?:\\\\"|[^"])+)"|(\w+))\s*=>\s*(?:"((?:\\\\"|[^"])*)"|(NULL))/ms',
        $data, $matches, PREG_SET_ORDER);
    $hstore = array();
    foreach ($matches as $set) {
        $key = $set[1] ? $set[1] : $set[2];
        $val = $set[4]=='NULL' ? null : $set[3];
        $hstore[$key] = $val;
    }
    return $hstore;
}

It would properly recognize the following string:

"a"=>"1", "b"=>"", "c"=>NULL, "d"=>"Some \"quoted\" value"

Pomm's HStore converter has been fixed.

Usage:

hstore(<some_array_or_object>, false) converts the input to a valid hStore string literal and returns it:

hstore(array('k1' => 'v1', 'k2' => 'v2')) => "k1"=>"v1","k2"=>"v2"

hstore(<some_array_or_object>) converts the input to a valid hStore, single-quoted, followed by ::hstore

hstore(array('k1' => 'v1', 'k2' => 'v2')) => '"k1"=>"v1","k2"=>"v2"'::hstore

hstore(<some_string>) converts from an hstore string (as it comes from a query) to array

hstore('"k1"=>"v1","k2"=>"v2"') => array('k1' => 'v1', 'k2' => 'v2')

It handles NULLs (both ways), and properly escapes/unescapes keys and values.

<?php


/**
 * mixed hstore(mixed $input[, bool $prepared = false])
 *      Convert from hstore string to array, or from array/object to hstore.
 * Inner arrays/objects are serialized but deserialization is up to you; you
 * are expected to keep track of which fields you sent as non-scalars.
 *
 * @param mixed $input          A string (from hstore) or an array/object
 * @param type $prepared        Array or object to convert to hstore string
 * @return mixed                Depends on the input
 */
function hstore($input, $prepared=false)
{
    if (is_string($input))
    {
        if ($input === 'NULL')
        {
            $output = NULL;
        }
        else
        {
        $re = '_("|^)(.*?[^\\\\"])"=>"(.*?[^\\\\"])("|$)_s';
        preg_match_all($re, $input, $pairs);
        $mid = $pairs ? array_combine($pairs[2], $pairs[3]) : array();

            foreach ($mid as $k => $v)
            {
                $output[trim($k, '"')] = stripslashes($v);
            }
        }
    }
    elseif (is_null($input))
    {
        $output = $prepared  ? 'NULL::hstore' : 'NULL';
    }
    elseif (!is_scalar($input))
    {
        foreach ((array)$input as $k => $v)
        {
            !is_scalar($v) && ($v = serialize($v));
            $entries[] = '"' . addslashes($k) . '"=>' .
                         '"' . addslashes($v) . '"';
        }

        $mid = empty($entries) ? '' : join(', ', $entries);

        $output = $prepared ? "'{$mid}'::hstore" : $mid;
    }

    return $output;
}

?>

edit: added 's' switch to the regex, to correctly handle linefeed within keys or values

Try this:

<?php
function encode_hstore($array) {
    if (!$array)
        return NULL;

    if (!is_array($array))
        return $array;

    $expr = array();

    foreach ($array as $key => $val) {
        $search = array('\\', "'", '"');
        $replace = array('\\\\', "''", '\"');

        $key = str_replace($search, $replace, $key);
        $val = $val === NULL
             ? 'NULL'
             : '"'.str_replace($search, $replace, $val).'"';

        $expr[] = sprintf('"%s"=>%s', $key, $val);
    }

    return sprintf("'%s'::hstore", implode(',', $expr));
}

function decode_hstore($hstore) {
    if (!$hstore || !preg_match_all('/"(.+)(?<!\\\)"=>(""|NULL|".+(?<!\\\)"),?/U', $hstore, $match, PREG_SET_ORDER))
        return array();

    $array = array();

    foreach ($match as $set) {
        list(, $k, $v) = $set;

        $v = $v === 'NULL'
           ? NULL
           : substr($v, 1, -1);

        $search = array('\"', '\\\\');
        $replace = array('"', '\\');

        $k = str_replace($search, $replace, $k);
        if ($v !== NULL)
            $v = str_replace($search, $replace, $v);

        $array[$k] = $v;
    }

    return $array;
}

$dsn = 'pgsql:host=127.0.0.1;dbname=test';
$user = 'user';
$pass = 'pass';
$pdo = new \PDO($dsn, $user, $pass);

$data = array(
    'k1' => 'v1',
    'k2' => NULL,
    'k3' => 'NULL',
    'k4' => '"v4',
    'k5' => 'a\'b"c\\d,e',
    'k6' => '"k1"=>"v1", ',
    'k7"=>' => 'v7',
    'k8' => '',
);
var_dump($data);

$expr = encode_hstore($data);
echo $expr . PHP_EOL;

$encoded = $pdo->query("select {$expr}")->fetchColumn();
echo $encoded . PHP_EOL;

$decoded = decode_hstore($encoded);
var_dump($decoded);

Another solution:

function convertHstoreToArray($hstoreString) {
    $hstoreArray = array();
    // explode array elements
    $keyValueStringsArray = explode(', ', $hstoreString);
    foreach($keyValueStringsArray as $keyValueString) {
        // trim first and last "
        $keyValueString = substr($keyValueString, 1, -1);
        // explode key and value
        $keyValueArray = explode('"=>"', $keyValueString);
        $hstoreArray[$keyValueArray[0]] = $keyValueArray[1];
    }
    return $hstoreArray;
}

or

function convertHstoreToArray($hstoreString) {
    $hstoreArray = array();
    // explode array elements
    $keyValueStringsArray = explode(', ', $hstoreString);
    foreach($keyValueStringsArray as $keyValueString) {
        // explode key and value
        $keyValueArray = explode('=>', $keyValueString);
        $key = $keyValueArray[0];
        // trim first and last "
        $key = substr($key, 1, -1);
        $value = $keyValueArray[1];
        if ($value === 'NULL') {
            $value = null;
        } else {
            // trim first and last "
            $value = substr($value, 1, -1);
        }
        $hstoreArray[$key] = $value;
    }
    return $hstoreArray;
}

If you need to convert a string in PHP (not in db request) you can use following prepared statement (inspired by cwallenpoole's solution):

select (each(h)).key, (each(h)).value from (select ?::hstore as h) as s

You can use any PHP variable in this query:

$st = $db->prepare("select (each(h)).key, (each(h)).value from (select ?::hstore as h) as s"); $st->execute(array('"abc"=>"123", "def"=>"654"'); $out = $st->fetchAll(PDO::FETCH_KEY_PAIR);