Support JSON data type in latest MySQL, Oracle, PostgreSQL

[fproject]

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?

[nineinchnick]

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

[samdark]

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

[fproject]

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...

[cebe]

somehow related to #4899

[SamMousa]

Bump!

[IStranger]

I think, it's gonna be amazing something like this:

User::find()
   ->andWhere(['json_column->email' => 'mail@example.com'])
   ->one();

User::find()
   ->andWhere(['json_column->email' => ['mail1@example.com', 'mail2@example.com']])
   ->orderBy(['json_column->additionalData->registeredAt' => SORT_DESC])
   ->all();

SQL:

SELECT * FROM user 
WHERE json_column->"$.email" = "mail@example.com";

SELECT * FROM user 
WHERE json_column->"$.email" IN ("mail1@example.com", "mail2@example.com")
ORDER BY json_column->"$.additionalData.registeredAt" DESC;

FYI: Similar feature already exists in laravel: https://laravel.com/docs/5.3/queries#json-where-clauses

[samdark]

Yes, would be good to have.

[SamMousa]

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

[samdark]

No idea.

[nguyenbs]

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)

[SilverFire]

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.

[sergeymakinen]

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

[samdark]

Note that in 2.1 MSSQL and Oracle are separate extensions:

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

[tunecino]

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' => 'mail@example.com'])])
    ->one();

I'm no expert but it seems to be the correct operator to use with pgsql according to those benchmarks.

[phplego]

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