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?

  • xylogx@lemmy.worldEnglish
    1·
    5 hours ago

    So you can bring json into a spreadsheet as ling as you are cool with some if your cells containing multi-valued entries. The question is how do you want those multi valued fields to o be handled? How are they displayed? How would sorting and filtering on these fields work?

    • early_riser@lemmy.radioOP
      4·
      1 day ago

      I just checked this out. It’s not quite what I’m looking for right now but it does answer my question as asked. I can see it coming in handy later.

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

  • Em Adespoton@lemmy.ca
    6·
    2 days ago

    I’ll second the SQL database here. Especially since most people who use a spreadsheet actually treat it as a database in the first place, and not as a way to lay out data in a 2D table.

    But if a hierarchical table is really what’s desired, any visual database interface should do the trick.

  • Onno (VK6FLAB)@lemmy.radio
    5·
    2 days ago

    There’s a whole range of cli tools to extract and query structured data like that, but you might consider loading it into something like sqlite3 and treating it as a database because those formats are really not intended for queries, they’re designed for sharing data.