I want to create, sort, filter, query, update, etc. hierarchical data like JSON or XML or YAML with the same ease as a spreadsheet. Does such a thing exist?

  • my_hat_stinks@programming.dev
    6·
    2 days 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.

    • Zwuzelmaus@feddit.org
      1·
      2 days ago

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

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

      • davel@lemmy.mlEnglish
        1·
        1 day 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.