46
Everyone saying relational databases but MongoDB is literally a json document database
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.
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.
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.
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": "[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:
Tables
dbo.users
| user_id | name | |
|---|---|---|
| 1 | Alice | [email protected] |
| 2 | Bob | [email protected] |
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! :-))
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?
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.
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.
TreeSheets?
jq might work.
Closest thing I know is Grist (https://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.
Visidata, maybe.
Visual Studio Code with a jq plugin maybe
Ms Access?
this post was submitted on 28 Jun 2025
46 points (100.0% liked)
No Stupid Questions
41947 readers
722 users here now
No such thing. Ask away!
!nostupidquestions is a community dedicated to being helpful and answering each others' questions on various topics.
The rules for posting and commenting, besides the rules defined here for lemmy.world, are as follows:
Rules (interactive)
Rule 1- All posts must be legitimate questions. All post titles must include a question.
All posts must be legitimate questions, and all post titles must include a question. Questions that are joke or trolling questions, memes, song lyrics as title, etc. are not allowed here. See Rule 6 for all exceptions.
Rule 2- Your question subject cannot be illegal or NSFW material.
Your question subject cannot be illegal or NSFW material. You will be warned first, banned second.
Rule 3- Do not seek mental, medical and professional help here.
Do not seek mental, medical and professional help here. Breaking this rule will not get you or your post removed, but it will put you at risk, and possibly in danger.
Rule 4- No self promotion or upvote-farming of any kind.
That's it.
Rule 5- No baiting or sealioning or promoting an agenda.
Questions which, instead of being of an innocuous nature, are specifically intended (based on reports and in the opinion of our crack moderation team) to bait users into ideological wars on charged political topics will be removed and the authors warned - or banned - depending on severity.
Rule 6- Regarding META posts and joke questions.
Provided it is about the community itself, you may post non-question posts using the [META] tag on your post title.
On fridays, you are allowed to post meme and troll questions, on the condition that it's in text format only, and conforms with our other rules. These posts MUST include the [NSQ Friday] tag in their title.
If you post a serious question on friday and are looking only for legitimate answers, then please include the [Serious] tag on your post. Irrelevant replies will then be removed by moderators.
Rule 7- You can't intentionally annoy, mock, or harass other members.
If you intentionally annoy, mock, harass, or discriminate against any individual member, you will be removed.
Likewise, if you are a member, sympathiser or a resemblant of a movement that is known to largely hate, mock, discriminate against, and/or want to take lives of a group of people, and you were provably vocal about your hate, then you will be banned on sight.
Rule 8- All comments should try to stay relevant to their parent content.
Rule 9- Reposts from other platforms are not allowed.
Let everyone have their own content.
Rule 10- Majority of bots aren't allowed to participate here. This includes using AI responses and summaries.
Credits
Our breathtaking icon was bestowed upon us by @Cevilia!
The greatest banner of all time: by @TheOneWithTheHair!
founded 2 years ago
MODERATORS