Mysql – How To Store EAV Data When It Has Nested Objects/Entities

eavMySQL

I have json data that have both nested objects and attributes. Eg of one json object:

(After json_decode into an array:)

Array
(
    [attributename1] => attributevalue1
    [attributename2] => attributevalue2
    [NestedEntity1] => Array (

                [0] => Array (
                            [attributename1] => attributevalue1
                            [attributename2] => attributevalue2
                             )
                [1] => Array (
                            [attributename1] => attributevalue1
                            [attributename2] => attributevalue2
                             )
                [2] => Array (
                            [attributename1] => attributevalue1
                            [attributename2] => attributevalue2
                            [NestedEntity1.2] => Array (

                                    [0] => Array (
                                                [attributename1] => attributevalue1
                                                [attributename2] => attributevalue2
                                                 )
                                    [1] => Array (
                                                [attributename1] => attributevalue1
                                                [attributename2] => attributevalue2
                                                 )

                             )

                  )

) )

Note: entities can be further nested in nested entities

My question is: How can I store this data into MySQL?

If it's just attributes, it's easy with a table in MySQL set up for EAV data. But if I have nested objects, I am quite lost as to how to store this. An important thing is that I'd have to be able to pivot the table and make queries like "SELECT * FROM entity WHERE A LIKE B etc.

I suspect I might have to split the nested entities into new tables of their own, then create one-to-many relationship between the "parent" and "child". However:
(1) I have no clue as to do this such that any json data can be accomodated. This probably means some program has to detect automatically a nested object.
(2) ALthough I am open to populating the data form a single json object into multiple tables, one representing each (nested) entity, I was thinking if it might be possible to populate everything into a single table in MySQL. This would prevent the need for creating new table schemas when the need arises.

Best Answer

Proposed data structure is a tree. You have the root node having leaves and trunks attached to it. Each trunk also have a leaves/trunks. Here leaves are the attributes/values while trunks are the elements of the tree. You need at least two tables for the Tree and EAV stored separately:

Tree                EAV
+----+--------+     +----+---------+------+-------+
| id | parent |     | id | tree_id | attr | value |
+----+--------+     +----+---------+------+-------+

Here Tree stores the structure/subordinance while EAV stores the items within the structure. An exact implementation is not so complicated with modern versions of MySQL/MariaDB having recursive CTEs.

Related Question