MySQL 5.7 JSON Data Type

It looks like Phalcon does not support the new Json data type that is now available in MySQL 5.7. As of right now, using this datatype simply returns "Trying to call method numrows on a non-object" on any database lookup.

Does anyone know if they are working to add support for MySQL Json?

edited Jun '16

Phalcon ORM is designed to be DB independent, so I doubt there is any plan to support it...

I don't know the specifics of the new JSON type, but maybe you could implement the desired functionality by using a dialect class: https://github.com/phalcon/incubator/blob/master/Library/Phalcon/Db/Dialect/MysqlExtended.php

Then register the class in the db service:

$di->set('db', function(){)
    // ...
    $db->setDialect(new MysqlExtended);
    return $db;
});

Thanks for your response. I've added the JSON_EXTRACT function as a custom dialect. When I run the following, I don't get an error, but just an empty array. I would have expected an error at least. Any insight at all is much appreciated?

$mysqlSettings['host'] = 'xxx';
$mysqlSettings['port'] = 'xxx';
$mysqlSettings['password'] = 'xxx'
$mysqlSettings['username'] = 'xxx';
$mysqlSettings['dbname'] = 'xxx';

$mysqlSettings['options'] = [
    \PDO::ATTR_EMULATE_PREPARES     => false,
    \PDO::ATTR_STRINGIFY_FETCHES    => false,
    \PDO::ATTR_ERRMODE              => \PDO::ERRMODE_EXCEPTION
];

$dialect = new \Phalcon\Db\Dialect\MySQL();
$dialect->registerCustomFunction(
    'JSON_EXTRACT',
    function($dialect, $expression) {
        $arguments = $expression['arguments'];
        return sprintf(
            "JSON_EXTRACT(%s, %s)",
            $dialect->getSqlExpression($arguments[0]),
            $dialect->getSqlExpression($arguments[1])
        );
    }
);
$mysqlSettings['dialectClass'] = $dialect;

$db = new \Phalcon\Db\Adapter\Pdo\Mysql($mysqlSettings);
print_r($db->fetchAll("SELECT JSON_EXTRACT('[10, 20, [30, 40]]', '$[1]');")); // from mysql docs


48.3k
edited Jun '16

Check out the generated SQL with getSql()["sql"]. You'd need to setup a query. There are all kinds of ways to setting it up so just work with it until you can get to a query object.