• 1 Vote(s) - 5 Average
  • 1
  • 2
  • 3
  • 4
  • 5
MySQL JSON fields support in query builder

#1
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? 
Reply

#2
Sounds great. Also looking for this solution (feature).
"Who thinks in code"
Perfectly describes who I am
mbparvez.me
Reply

#3
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.
Reply

#4
(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 Wink
Reply

#5
This should help:

A Practical Guide to MySQL JSON Data Type By Example
What did you Try? What did you Get? What did you Expect?

Joined CodeIgniter Community 2009.  ( Skype: insitfx )
Reply

#6
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.
Reply

#7
I'll take a closer look at it in the morning to get the full grasp of it.
What did you Try? What did you Get? What did you Expect?

Joined CodeIgniter Community 2009.  ( Skype: insitfx )
Reply

#8
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.
What did you Try? What did you Get? What did you Expect?

Joined CodeIgniter Community 2009.  ( Skype: insitfx )
Reply


Digg   Delicious   Reddit   Facebook   Twitter   StumbleUpon  


  Theme © 2014 iAndrew  
Powered By MyBB, © 2002-2020 MyBB Group.