Is there something like a spreadsheet for hierarchical data structures?
from early_riser@lemmy.radio to nostupidquestions@lemmy.world on 28 Jun 01:16
https://lemmy.radio/post/8082139
from early_riser@lemmy.radio to nostupidquestions@lemmy.world on 28 Jun 01:16
https://lemmy.radio/post/8082139
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?
#nostupidquestions
threaded - newest
Visual Studio Code with a jq plugin maybe
TreeSheets?
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.
Visidata, maybe.
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.
That’s generally what relational databases are for. You might try LibreOffice Base or sqlite.
As for JSON, XML, and YAML files in particular, there are tools for doing one-off queries/transformations against them, like jq and yq.
But they need rectangular structure. How do they work on tree structures, like OP has asked?
Again, that wasn’t the question.
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.
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.
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! :-))
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.
Ms Access?
Closest thing I know is Grist (www.getgrist.com), because columns can contain lists of values or lists of references.
Probably not exactly what you’re looking for, but it served both my wife and I very well.
TreeSheets
One of the best organising tools I ever found. And still under active development.
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.
jq might work.
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?
Everyone saying relational databases but MongoDB is literally a json document database