db
db copied to clipboard
yiisoft
Support JSON data type in latest MySQL, Oracle, PostgreSQL
The latest updates from several DB such as MySQL, Oracle, PostgreSQL are all supporting JSON data type with basic operations: create, update, index, validate.
Should we implement a support at PHP layer of Yii Framework to support those JSON operations?
MySQL does, but MariaDB doesn't yet. Can you give examples of how the JSON format could be utilized?
I think MariaDB does something like that as well: https://mariadb.com/kb/en/mariadb/dynamic-columns/
I think our Yii2.x ActiveRecord and ActiveQuery should support methods for handling JSON data.
Suppose that we have an active record $user from User model class with userId =1 and a column jsonData in type of that SQL JSON, that has following value:
{
"profile": {
"recentActions": [
{ "name": "loggedIn", "time":"20151223T010203Z"},
{ "name": "loggedOut", "time":"20151223T010203Z"},
{ "name": "comment", "time":"20151223T010203Z"}
]
},
"someOtherData": {...}
}
- When you want to find all recent actions, just use something like this:
$actions = $user->jsonExtract("jsonData$.profile.recentActions");
The generated MySQL query should be:
SELECT JSON_EXTRACT(`jsonData`, '$.profile.recentActions') as `jsonData` FROM `user` WHERE `ID`=1;
- When you want to query some part of that jsonData together with some other fields of
Usertable, just use something like this:
$users = User::find()->jsonExtract("jsonData$.profile.recentActions")->all();
which generates following SQL:
SELECT `id`, `name`, JSON_EXTRACT(`jsonData`, '$.profile.recentActions') as `jsonData` FROM `user`;
More complex, you can use:
$users = User::find()->where("jsonLength(jsonExtract(jsonData$.profile.recentActions)) > :count",[":count"=>0])->jsonExtract("jsonData$.profile.recentActions")->all();
to generate following SQL:
SELECT `id`, `name`, JSON_EXTRACT(`jsonData`, '$.profile.recentActions') as `jsonData` FROM `user` WHERE JSON_LENGTH(JSON_EXTRACT(jsonData, "$.profile.recentActions")) > 0;
- When you want to set a value to JSON data, just use some thing like this:
$return = $user->jsonSet("jsonData$.profile.recentActions[0].time", "20171223T010203Z");
Then the generated MySQL query should be:
UPDATE `user` SET `jsonData` = JSON_SET(`jsonData`, '$.profile.recentActions[0].time') WHERE `ID`=1;
- ... And so on...
I think, it's gonna be amazing something like this:
User::find()
->andWhere(['json_column->email' => '[email protected]'])
->one();
User::find()
->andWhere(['json_column->email' => ['[email protected]', '[email protected]']])
->orderBy(['json_column->additionalData->registeredAt' => SORT_DESC])
->all();
SQL:
SELECT * FROM user
WHERE json_column->"$.email" = "[email protected]";
SELECT * FROM user
WHERE json_column->"$.email" IN ("[email protected]", "[email protected]")
ORDER BY json_column->"$.additionalData.registeredAt" DESC;
FYI: Similar feature already exists in laravel: https://laravel.com/docs/5.3/queries#json-where-clauses
How about the progress of this issue? I'm the one that created this (by my other account but now I didn't use it anymore and deleted it)
In Yii 2.0.14 we've implemented JSON support for MySQL and PostgreSQL. Unfortunately, nobody in our core team uses Oracle on daily basis, so the OCI implementation is delayed. In case anybody in this thread works with Oracle and would like to help - let me know, I'll provide a starting point.
Note that in 2.1 MSSQL and Oracle are separate extensions:
- https://github.com/yiisoft/yii2-mssql
- https://github.com/yiisoft/yii2-oracle
That's just SQL part though; I think that part works already?
Niet. At least not with PostgreSQL. But this one did:
User::find()
->where(['@>', 'json_column', new JsonExpression(['email' => '[email protected]'])])
->one();
I'm no expert but it seems to be the correct operator to use with pgsql according to those benchmarks.
Is there any workaround for JSON type nowdays? GridView and form inputs throws "array to string conversion" notice and gets "Array" into value.
