Spyke
Einar
lemmy.zip

TreeSheets

One of the best organising tools I ever found. And still under active development.

14
early_riserreply
lemmy.radio

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.

4
Zwuzelmausreply
feddit.org

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.

0
my_hat_stinksreply
programming.dev

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:

::: spoiler JSON

  "users": [
    {
      "id": 1,
      "name": "Alice",
      "email": "[email protected]",
      "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": "[email protected]",
      "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:

::: spoiler Tables

dbo.users

user_idnameemail
1Alice[email protected]
2Bob[email protected]

dbo.games

game_idtitlegenre
1The Witcher 3RPG
2MinecraftSandbox
3FortniteBattle Royale
4Rocket LeagueSports

dbo.favorites

user_idgame_id
11
12
23
24

dbo.platforms

platform_idgame_idnamerelease_yearrating
11PC20159.8
21PS420159.5
32PC20119.2
42Xbox One20149.0
53PC20178.6
63PS520208.5
74PC20158.8
84Switch20178.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.

6
Zwuzelmausreply
feddit.org

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! :-))

1
davel [he/him]reply
lemmy.ml

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.

1
Em Adespoton
lemmy.ca

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.

6
Onno (VK6FLAB)
lemmy.radio

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.

5
GreatRam
lemmy.world

Everyone saying relational databases but MongoDB is literally a json document database

2
xylogx
lemmy.world

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?

1

You reached the end

Is there something like a spreadsheet for hierarchical data structures? | Spyke