Comment on Is there something like a spreadsheet for hierarchical data structures?

davel@lemmy.ml ⁨19⁩ ⁨hours⁩ ago

• Zwuzelmaus@feddit.org ⁨16⁩ ⁨hours⁩ ago

  That’s generally what relational databases are for.

  But they need rectangular structure. How do they work on tree structures, like OP has asked?

  one-off queries/transformations

  Again, that wasn’t the question.

  source

  • my_hat_stinks@programming.dev ⁨14⁩ ⁨hours⁩ ago

    The question reads like an XY problem, they describe DB functions for data structures so unless there’s some specific reason they can’t use a DB that’s the right answer. A “spreadsheet for data structures” describes a relational database.

    But they need rectangular structure. How do they work on tree structures, like OP has asked?

    Relationships. You don’t dump all your data in a single table. Take for instance the following sample JSON:

    JSON

    ___ { “users”: [ { “id”: 1, “name”: “Alice”, “email”: “alice@example.com”, “favorites”: { “games”: [ { “title”: “The Witcher 3”, “platforms”: [ { “name”: “PC”, “release_year”: 2015, “rating”: 9.8 }, { “name”: “PS4”, “release_year”: 2015, “rating”: 9.5 } ], “genres”: [“RPG”, “Action”] }, { “title”: “Minecraft”, “platforms”: [ { “name”: “PC”, “release_year”: 2011, “rating”: 9.2 }, { “name”: “Xbox One”, “release_year”: 2014, “rating”: 9.0 } ], “genres”: [“Sandbox”, “Survival”] } ] } }, { “id”: 2, “name”: “Bob”, “email”: “bob@example.com”, “favorites”: { “games”: [ { “title”: “Fortnite”, “platforms”: [ { “name”: “PC”, “release_year”: 2017, “rating”: 8.6 }, { “name”: “PS5”, “release_year”: 2020, “rating”: 8.5 } ], “genres”: [“Battle Royale”, “Action”] }, { “title”: “Rocket League”, “platforms”: [ { “name”: “PC”, “release_year”: 2015, “rating”: 8.8 }, { “name”: “Switch”, “release_year”: 2017, “rating”: 8.9 } ], “genres”: [“Sports”, “Action”] } ] } } ] }

    You’d structure that in SQL tables something like this:

    Tables

    ___ dbo.users | user_id | name | email | | -------- | ----- | --------------------------------------------- | | 1 | Alice | alice@example.com | | 2 | Bob | bob@example.com | dbo.games | game_id | title | genre | | -------- | ------------- | ------------- | | 1 | The Witcher 3 | RPG | | 2 | Minecraft | Sandbox | | 3 | Fortnite | Battle Royale | | 4 | Rocket League | Sports | dbo.favorites | user_id | game_id | | -------- | -------- | | 1 | 1 | | 1 | 2 | | 2 | 3 | | 2 | 4 | dbo.platforms | platform_id | game_id | name | release_year | rating | | ------------ | -------- | ------------- | ------ | -------- | | 1 | 1 | PC | 2015 | 9.8 | | 2 | 1 | PS4 | 2015 | 9.5 | | 3 | 2 | PC | 2011 | 9.2 | | 4 | 2 | Xbox One | 2014 | 9.0 | | 5 | 3 | PC | 2017 | 8.6 | | 6 | 3 | PS5 | 2020 | 8.5 | | 7 | 4 | PC | 2015 | 8.8 | | 8 | 4 | Switch | 2017 | 8.9 |

    The dbo.favorites table handles the many-to-many relationship between users and games; users can have as many favourite games as they want, and multiple users can have the same favourite game. The dbo.platforms handles one-to-many relationships; each record in this table represents a single release, but each game can have multiple releases on different platforms.

    source

    • Zwuzelmaus@feddit.org ⁨14⁩ ⁨hours⁩ ago

      So the real question was, which tool to use in order to transform the JSON’s tree Ingo these tables & relations?

      (hopefully you didn’t just write this all up manually! :-))

      source

      • davel@lemmy.ml ⁨3⁩ ⁨hours⁩ ago

        There are tools out there to generate a SQL script from a JSON file that contains all the necessary DDL and DML statements to produce a database in full. I’m not familiar with any of them, though, so I can’t help there.

        source