CODEWITHSUNDEEP
phppostgresql
archmeta
archmeta

Reputation: 1127

Convert postgresql hstore to php array

Is there a good php code snippet to convert a postgresql hstore to a php array, that will correctly translate an unquoted NULL within the hstore to a php NULL?

EG: Suppose we have the following hstore string:

"k1"=>"v1", "k2"=>NULL, "k3"=>"NULL", "k4"=>"\"v4"
(aka SELECT '"k1"=>"v1","k2"=>NULL,"k3"=>"NULL","k4"=>"\\"v4"'::hstore;)

How can we convert this into the following php array?

array('k1' => 'v1', 'k2' => NULL, 'k3' => 'NULL', 'k4' => '\"v4');

I following the following converter but it does not seem to handle the unquoted NULL: https://github.com/chanmix51/Pomm/blob/master/Pomm/Converter/PgHStore.php

Upvotes: 10

Views: 5720

Answers (11)

Blafasel42
Blafasel42

Reputation: 355

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...

Upvotes: 3

Eddie
Eddie

Reputation: 1643

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);

Upvotes: 0

PeterTheOne
PeterTheOne

Reputation: 343

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;
}

Upvotes: 0

hegemon
hegemon

Reputation: 6764

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"

Upvotes: 1

yangyi
yangyi

Reputation: 1

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);

Upvotes: 0

deceze
deceze

Reputation: 522519

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.

Upvotes: 1

Joshua Burns
Joshua Burns

Reputation: 8582

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.

Upvotes: 2

user832146
user832146

Reputation: 150

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

Upvotes: 0

Loading
Loading

Reputation: 109

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

Upvotes: 2

greg
greg

Reputation: 17

Pomm's HStore converter has been fixed.

Upvotes: 0

cwallenpoole
cwallenpoole

Reputation: 82058

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 ];
}

Upvotes: 5

Related Questions