We have moved our forum to GitHub Discussions. For questions about Phalcon v3/v4/v5 you can visit here and for Phalcon v6 here.

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


1.4k

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