Dynamic columns MariaDB and JSON?

Question

I am trying to store JSON data (pretty much an array in PHP, which may be of unknown length) in a column that will allow me to make queries on it. Let me use this as an example ...

Say that I have a box asking what kind of sport you like (and this is a check box).

[] Soccer [] Hockey [] Football [] Swimming 

which will then be sent through the sports[] array in PHP. Then I want to take this and add it to my database so that it looks like

 |sports|{hockey:false,swimming:true,football:false,soccer:true}| 

Or something like that, then I could go

 SELECT COUNT(*) FROM fields WHERE hockey = "false"; 

And I can’t understand how to do it right, ignore the fact that this “example” could be easily done by joining it, I want to do this using the mariadb dynamic columns (at least I believe that, what i want). I know I can do this with Postgre.

Answer 1

Everything is here: https://mariadb.com/kb/en/dynamic-columns/

Dynamic columns are stored in a real blob column and inserted like this:

 insert into data values (data_id, user_id, COLUMN_CREATE(<dcolumn1>, <value1>, <dcolumn2>, <value2>, ...)); 

So you would not use json here, but serialize the data like this:

 $dynamic = array() foreach($_POST['sports'] as $sport) { $dynamicData[] = "'" . $sport . "'", TRUE; } $dynamicColumn = join(', ', $dynamicData); 

Will generate something like

 'hockey', TRUE, 'soccer', TRUE 

To search all lines for playing plp football:

 ... where COLUMN_GET(dynamic_blob, 'soccer' as soccer) = TRUE;