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?

  • Zwuzelmaus@feddit.org
    11·
    2 days 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.

    • 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.