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

Question

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.

• Jasen: why json_decode?
• Rick James: EAV is a bad schema pattern. You are asking for something worse. Go with JSON and forget about EAV. OTOH, if, instead of posing an abstract question, you presented a real dataset, we might help you find an alternative solution.
• Kondybas: @RickJames EAV combined with the tree hierarcy is not so bad and can be efficient enough. Say, postal address in all its variety.
• user1822: Why not create a single column that stores that as a JSON value?

Answer 1

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.