Most database systems have now native support for JSON format. Some of
them, like PostgreSQL, also accepts arrays. I rememeber there was some
fork of pgsql extension with native array support.
Maybe it is time to extend PDO so it can accept at least one of these
formats? What do You think?
Most database systems have now native support for JSON format. Some of
them, like PostgreSQL, also accepts arrays. I rememeber there was some
fork of pgsql extension with native array support.Maybe it is time to extend PDO so it can accept at least one of these
formats? What do You think?
Hi,
What would "support" exactly mean for a JSON type? Since JSON is a way of serialising data into a string, wouldn't all JSON values going into and out of the database just look like strings to the driver?
For Postgres's array types, some support for serialising and unserialising would definitely be useful, though, because the format is rather complex, particularly if you have multidimensional and sparse arrays. Obviously, users now have the workaround of transferring as JSON, but I know my last company had a pair of utility functions to attempt this in userland, which were known to be buggy in certain edge cases.
Regards,
--
Rowan Collins
[IMSoP]
Hi,
What would "support" exactly mean for a JSON type? Since JSON is a
way of serialising data into a string, wouldn't all JSON values going
into and out of the database just look like strings to the driver?For Postgres's array types, some support for serialising and
unserialising would definitely be useful, though, because the format is
rather complex, particularly if you have multidimensional and sparse
arrays. Obviously, users now have the workaround of transferring as
JSON, but I know my last company had a pair of utility functions to
attempt this in userland, which were known to be buggy in certain edge
cases.Regards,
JSON should be automatically converted to proper type(s).
$data = ['name' => 'John'];
$sth = $dbh->prepare('INSERT INTO table (json_column) values (:data)');
$sth->bindParam(':data', $data, PDO::PARAM_JSON); // new parameter
SELECT json_column FROM table;
$stmt->fetch(PDO::FETCH_ASSOC) should return
array(
[json_column] => array('name' => 'John')
)
.
JSON should be automatically converted to proper type(s).
$data = ['name' => 'John'];
$sth = $dbh->prepare('INSERT INTO table (json_column) values
(:data)');
$sth->bindParam(':data', $data, PDO::PARAM_JSON); // new parameterSELECT json_column FROM table;
$stmt->fetch(PDO::FETCH_ASSOC) should returnarray(
[json_column] => array('name' => 'John')
)
Mind that such conversions are not 100% reliable:
php > var_dump(json_decode('{"a": 10.0000000000000000001}'));
object(stdClass)#1 (1) {
["a"]=>
float(10)
}
php > echo json_encode(json_decode('{"a": 10.0000000000000000001}'));
{"a":10}
php > var_dump(json_decode(json_encode(json_decode('{"a": 10.0000000000000000001}'))));
object(stdClass)#1 (1) {
["a"]=>
int(10)
}
johannes
JSON should be automatically converted to proper type(s).
$data = ['name' => 'John'];
$sth = $dbh->prepare('INSERT INTO table (json_column) values (:data)');
$sth->bindParam(':data', $data, PDO::PARAM_JSON); // new parameterSELECT json_column FROM table;
$stmt->fetch(PDO::FETCH_ASSOC) should returnarray(
[json_column] => array('name' => 'John')
)
This feels like unnecessary magic to me, when you can already write this, and have all the power and flexibility of the existing JSON functions:
$data = ['name' => 'John'];
$sth = $dbh->prepare('INSERT INTO table (json_column) values (:data)');
$sth->bindParam(':data', json_encode($data), PDO::PARAM_STRING);
As I say, array types are much more fiddly, so support for those would be great - that could just be well-tested encode /decode functions.
Regards,
--
Rowan Collins
[IMSoP]
Encoding data to insert query is simple. But selecting data requires
additional magic and processing.
Encoding data to insert query is simple. But selecting data requires
additional magic and processing.
The magic while reading is quite limited - at least for MySQL the
protocol contains a flag in the meta data telling whether a field is a
"arbitrary" string field or a JSON field, based on that one could
decode accordingly.
The question is what the user expects ...
One fact is that applications are already using those types, changing
the default behavior would be a break. Also some users might want to
just pass it to a JS frontend and decoding and renencding for the
transport is a wast of cpu cycles. Similar more "administrative"
applications like phpMyAdmin need exta handling as the value can'T be
printed simply anymore.
Thus we'd need a special flag like
$pdo->fetch(PDO::FETCH_ASSOC | PDO::FETCH_DECODE_JSON)
and from there there is little step for leaving this completely to the
user ... they know what they need.
johannes
Hi,
I few years ago, I did a throwaway implementation for this for reading:
https://github.com/pilif/php-src/tree/pdo_pgsql-improvements
This supports columns of type jsonb, json and text[] and I was
considering also adding support for the various timestamps, date and
so on.
Generally, interest in such a feature was low, so I never completed
work on this, but maybe it's something you might want to play with.
Some more info here: https://blog.pilif.me/2014/03/28/pdo_pgsql-improvements/
Philip
Most database systems have now native support for JSON format. Some of them,
like PostgreSQL, also accepts arrays. I rememeber there was some fork of
pgsql extension with native array support.Maybe it is time to extend PDO so it can accept at least one of these
formats? What do You think?--
--
Sensational AG
Giesshübelstrasse 62c, Postfach 1966, 8021 Zürich
Tel. +41 43 544 09 60, Mobile +41 79 341 01 99
info@sensational.ch, http://www.sensational.ch