0

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.

  • why json_decode? – Jasen Jan 27 at 8:06
  • 1
    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. – Rick James Jan 28 at 4:14
  • @RickJames EAV combined with the tree hierarcy is not so bad and can be efficient enough. Say, postal address in all its variety. – Kondybas Jan 28 at 9:56
  • Why not create a single column that stores that as a JSON value? – a_horse_with_no_name Jul 26 at 9:34
0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.