Currently, I am using Joomlapolis community builder for my website, 'CB Field Group' add-on of community builder provides flexibility to the user to add a group of fields. Values of these group fields are stored in a single column and in a JSON format. I am using an older version 5.6 of MySQL and JSON commands not supported. I tried to change hosting but none of the hosting company provide MySQL version 5.7 with shared hosting and currently, I am not in a position to go for VSP hosting its very expensive. I wanted to change storage from JSON to a different table with the linkage of the primary key (user_id or id) in the plugin itself. It means wherever I create field group it will create a different table with the tab name.
Why do you need to change the storage? It's perfectly efficient being stored as JSON and there are no plans to change the storage behavior. There is no means of changing it without basically rewriting half the plugin. If you're not on MySQL 5.7+ it will use a TEXT column type instead of JSON column type and disable searching functionality. Beyond that it will work perfectly fine. The JSON column type is just a little more efficient at storing JSON and is required to be able to use JSON MYSQL functions against the data (used for searching).
Kyle (Krileon) Community Builder Team Member Before posting on forums:
Read FAQ thoroughly
+
Read our Documentation
+
Search the forums CB links:
Documentation
-
Localization
-
CB Quickstart
-
CB Paid Subscriptions
-
Add-Ons
-
Forge
-- If you are a Professional, Developer, or CB Paid Subscriptions subscriber and have a support issue please always post in your respective support forums for best results!
-- If I've missed your support post with a delay of 3 days or greater and are a Professional, Developer, or CBSubs subscriber please
send me a private message
with your thread and will reply when possible!
-- Please note I am available Monday - Friday from 8:00 AM CST to 4:00 PM CST. I am away on weekends (Saturday and Sunday) and if I've missed your post on or before a weekend after business hours please wait for the next following business day (Monday) and will get to your issue as soon as possible, thank you.
-- My role here is to provide guidance and assistance. I cannot provide custom code for each custom requirement. Please do not inquire me about custom development.
To extract what? You aren't going to extract individual field values from a JSON string efficiently using only SQL on MYSQL 5.6 as 5.6 does not have JSON functions. It is entirely possible to do on MYSQL 5.7+ since the JSON functions exist there. MYSQL 5.6 is end-of-life since last year with extended support ending 2021. Your host needs to be moving you to 5.7 or you should start considering finding a new host (note everyone should be on PHP 7.2 and MYSQL 5.7 minimum at this point otherwise you are significantly missing out on performance and functionality).
If your host won't update your MYSQL version then your only solution for extracting a value out of a JSON string with SQL only is functions like substring. See the below documentation on various string functions.
Kyle (Krileon) Community Builder Team Member Before posting on forums:
Read FAQ thoroughly
+
Read our Documentation
+
Search the forums CB links:
Documentation
-
Localization
-
CB Quickstart
-
CB Paid Subscriptions
-
Add-Ons
-
Forge
-- If you are a Professional, Developer, or CB Paid Subscriptions subscriber and have a support issue please always post in your respective support forums for best results!
-- If I've missed your support post with a delay of 3 days or greater and are a Professional, Developer, or CBSubs subscriber please
send me a private message
with your thread and will reply when possible!
-- Please note I am available Monday - Friday from 8:00 AM CST to 4:00 PM CST. I am away on weekends (Saturday and Sunday) and if I've missed your post on or before a weekend after business hours please wait for the next following business day (Monday) and will get to your issue as soon as possible, thank you.
-- My role here is to provide guidance and assistance. I cannot provide custom code for each custom requirement. Please do not inquire me about custom development.
I already told you, I tried to migrate another hosting but higher version is available only with VSP hosting (virtual private server) as evidence I am attaching here chat screenshots with very renowned hosting companies GoDaddy and Bluehost. GoDaddy provides 5.5 and Bluehost provide 5.6. However, you must be the aware cost of VSP hosting. if you know anyone who provides mysql 5.7 or higher version with shared hosting please let me know.
now coming to substring, substring function can't be used to extract JSON data because field value length can't be same every time and data can't be extracted for multiple group field data sets using substring so this solution is not at all workable or you provide me substring query for following JSON field.