group fields storage

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

If you are saying it will work then could you please let me know extract query for following json field.

Table name : jospt_comprofiler
colum name : cb_viifieldgroup
column value:

[{"cb_state":"Maharashtra","cb_district":"Solapur","cb_subdistrict":null,"cb_village":null,"cb_survenumber":"123","cb_gatanumber":"14","cb_upvibhag":"2","cb_khetraekaka":"","cb_hearchumi":"4"}]

make sure it should work in MYSQL Server version: 5.6.43

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.

dev.mysql.com/doc/refman/5.6/en/string-functions.html

If your host does upgrade you to MYSQL 5.7+ then see the below documentation on how to use its JSON functions.

dev.mysql.com/doc/refman/5.7/en/json-functions.html

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.