PDO native JSON / array support

  100042
July 26, 2017 10:25 aaatoja@o2.pl (=?UTF-8?Q?Micha=c5=82?=)
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?
  100043
July 26, 2017 12:42 rowan.collins@gmail.com (Rowan Collins)
On 26 July 2017 11:25:00 BST, "Michał" <aaatoja@o2.pl> wrote:
>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]
  100047
July 26, 2017 13:02 aaatoja@o2.pl (=?UTF-8?Q?Micha=c5=82?=)
> > 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') ) ..
  100049
July 26, 2017 15:43 johannes@schlueters.de (Johannes =?ISO-8859-1?Q?Schl=FCter?=)
On Mi, 2017-07-26 at 15:02 +0200, Michał wrote:
> > 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') > )
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
  100053
July 26, 2017 20:09 rowan.collins@gmail.com (Rowan Collins)
On 26 July 2017 14:02:03 BST, "Michał" <aaatoja@o2.pl> wrote:
>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') >)
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]
  100055
July 27, 2017 06:14 aaatoja@o2.pl (=?UTF-8?Q?Micha=c5=82?=)
Encoding data to insert query is simple. But selecting data requires 
additional magic and processing.
  100058
July 27, 2017 10:27 johannes@schlueters.de (Johannes =?ISO-8859-1?Q?Schl=FCter?=)
On Do, 2017-07-27 at 08:14 +0200, Michał wrote:
> 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
  100059
July 27, 2017 12:02 phofstetter@sensational.ch (Philip Hofstetter)
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

On Wed, Jul 26, 2017 at 12:25 PM, Michał <aaatoja@o2.pl> wrote:
> 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? > > -- > PHP Internals - PHP Runtime Development Mailing List > To unsubscribe, visit: http://www.php.net/unsub.php >
-- 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