+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: CodeIgniter 4 (https://forum.codeigniter.com/forumdisplay.php?fid=28)
+--- Forum: CodeIgniter 4 Feature Requests (https://forum.codeigniter.com/forumdisplay.php?fid=29)
+--- Thread: MySQL JSON fields support in query builder (/showthread.php?tid=76335)
MySQL JSON fields support in query builder - korgoth - 05-04-2020
Hello guys,
As of MySQL 5.7.8 there is a "JSON" data type supported in mysql databases.
It would be awesome if we get some query builder functions to work with those data types. I guess theres no way of that making it int he original builder driver, but maybe an Addin for that would be possible?
I find many many benefits in having this easy accessible from the query builder - what do you think? Do you have any ideas or thoughts on whether this is possible/doable?
RE: MySQL JSON fields support in query builder - webdevron - 08-15-2020
Sounds great. Also looking for this solution (feature).
RE: MySQL JSON fields support in query builder - MGatner - 08-15-2020
I don’t see any reason why this wouldn’t be part of the core. If it is an official MySQL data type then handling ought to be mandatory. How would this differ from a TEXT? Would the returned value be automatically run through json_decode()? Check out Entity casts for an example of handling this already at the entity level.
RE: MySQL JSON fields support in query builder - korgoth - 08-15-2020
(08-15-2020, 04:41 AM)MGatner Wrote: I don’t see any reason why this wouldn’t be part of the core. If it is an official MySQL data type then handling ought to be mandatory. How would this differ from a TEXT? Would the returned value be automatically run through json_decode()? Check out Entity casts for an example of handling this already at the entity level.
When getting it from the database its easy to use Entities that cast so you get decoded json. The thing is when building queries the JSON field in MySQL gives you the ability to search the JSON itself - perfom actions on nodes and stuff. Here are some example JSON queries to make things more clear: https://www.mysqltutorial.org/mysql-json/
i.e. it requires a whole new set of methods added to the builder imho - would be awesomely powerfull
RE: MySQL JSON fields support in query builder - InsiteFX - 08-15-2020
This should help:
A Practical Guide to MySQL JSON Data Type By Example
RE: MySQL JSON fields support in query builder - MGatner - 08-15-2020
Oh wow, yes that is quite powerful and quite complex. I would like to see native support for this but I agree, it would take some pretty heavy changes. It might be a good candidate to start as a separate module and eventually make its way in.
RE: MySQL JSON fields support in query builder - InsiteFX - 08-15-2020
I'll take a closer look at it in the morning to get the full grasp of it.
RE: MySQL JSON fields support in query builder - InsiteFX - 08-16-2020
MY intial take on this:
1) Would need to check the table for json column type.
2) To pull json data out of the table we would need to use the column path operator (->)
but then the data is surrounded by double quote marks ("data").
3) If the column type is json we would need to use the inline path operator to get the
column values (->>).
4) Queries would need to access the table data using the column-name and inline path operator (->>).
5) One problem I see here is the (column path operator ->) and the (inline path operator ->>)
(the -> and ->>) are going to interfere with the PHP operator (->).
5) This would also effect the DBForge.