Support JSON data type in latest MySQL, Oracle, PostgreSQL about active-record HOT 15 OPEN

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

from active-record.

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 User table, 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...

from active-record.

Note that in 2.1 MSSQL and Oracle are separate extensions:

• https://github.com/yiisoft/yii2-mssql
• https://github.com/yiisoft/yii2-oracle

from active-record.

Is there any workaround for JSON type nowdays? GridView and form inputs throws "array to string conversion" notice and gets "Array" into value.

from active-record.

Just in case: if noone makes a PR until April, I would implement it.

from active-record.

MySQL does, but MariaDB doesn't yet. Can you give examples of how the JSON format could be utilized?

from active-record.

I think MariaDB does something like that as well: https://mariadb.com/kb/en/mariadb/dynamic-columns/

from active-record.

somehow related to #4899

from active-record.

Bump!

from active-record.

Yes, would be good to have.

from active-record.

That's just SQL part though; I think that part works already?

from active-record.

No idea.

from active-record.

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)

from active-record.

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.

from active-record.

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.

from active-record.