Not trying to flame - I do realise that duckdb is an alternative, but everyone in this space does this
Especially for data scientists, etc. The tooling flow around dbt, pandas, jupyter, postgresql and json is rock solid.
It's exciting and brilliant technology. I believe the authors are the same ones behind MonetDB.
Please, flame away. I'm not here to put up a fight for one approach or another, simply interested in hearing what people think works well and what doesn't; what they'd expect/want.
How about "discuss away", heh.
You never hear hype about arangodb because arangodb users see it as a "secret weapon" and want their competitors to use mongo.
Yes (also known as jsonlines or line-separated JSON - .jsonl is the file extension).
I think JSON should be thought of as a message format, and messages should be small. Messages can be sent over a stream, without having the "whole" data-set. You also get append-only semantics on files (saving disk-space and virtually creating a stupid write-ahead log become as cheap & hassle-free as it gets), and if you need it you get resumability (say binary search on a file with monotonically increasing ids/timestamps etc).
Additionally, this format will work with minimal hickups when interoperating with other systems. YOU may have an advanced, efficient parser, but others may not. Most environments have a line-parser and a json deserializer. Large JSON-files become hard-to-manage and prompts for more and more complex query-like tools.
And all of these good properties from such a ridiculously simple tech that it can be explained in a short sentence.
Downsides:
- Compression at the file level removes resumability (unless your compression is also resumable).
- You may have to implement your own log compaction if you have many updates/deletes.
> * Which dialect for querying worked the best for you?
If this is SQL dialects, I am partial to both the Postgres JSON/JSONB operators, SQLite has a solid JSON implementation too. > * Which tools do you enjoy the most? This could be for any reason: convenience of install/availability, expressivity, raw speed, other niche features, etc.
"Datasette" (from Django co-creator) can take tabular data (SQLite, CSV, JSON, etc) and generate a REST/GraphQL API with visualization tools from it:https://github.com/simonw/datasette
From the same author, "sqlite-utils" generate SQLite table definitions and rows from similar:
https://github.com/simonw/sqlite-utils
I find this useful outside of SQLite because the syntax is similar across SQL databases. Great way to bootstrap a DB definition from existing datasets.
"Pipe JSON (or CSV or TSV) directly into a new SQLite database file, automatically creating a table with the appropriate schema"
> * What sort of JSON "meta-formats" are the most important/common for you? E.g. in a file you could have object-per-line, object-of-arrays, array-of-objects, or in an SQL context you could have object-per-row or object-of-arrays-as-table, etc). I'd love to hear about others that are important to you.
For file, either JSONL/NDJSON (JSON object per line) if it's large, or a single array of objects if it's small.In a SQL context, object-per-row undoubtedly
> * What do you feel is the biggest gap/missing feature in currently available tools?
Don't have anything useful on this one unfortunately, but I'm not an analytics/data science person =(If I think I'll have to do at least a few more analyses in the future, perhaps with a growing dataset, I'll usually put the data into Sqlite. If possible I try to keep it simple, with a single table, even if it means a non-normalized schema. As for tooling I typically go with `dataset`, an sqlalchemy wrapper that's super simple to use, and makes it possible to also use raw sql if I need to. I haven't fully explored the JSON capabilities of sqlite itself, but have been meaning to. If duckdb gets similar features that would be certainly worth looking into.
In terms of "meta-format", I usually like object-per-line and array of objects. Easy to add more records, pretty self-explanatory. Maybe inefficient but if that becomes an issue then it's time to move away from just JSON.
In regards to querying language, I usually don't do anything too complicated so I don't think much about it. Having SQL (as when using dataset) is nice. I also have to use mongodb for some tasks, and I also find that query language good enough for most things I need to do, since I'm not usually dealing with highly relational data.
Postgres JSONB works, but it requires maintaining a heavy server process. So does Lucene/elasticsearch.
I have been yearning for embeddable store (in line with SQLite the support that both works and also keeps the data compressed like JSONB). I know there were some attempts, tried some of it those, mostly monstrosities).
For those who don't know, JSONB is a binary JSON encoding that is specifically optimized for data at rest and compression thereof.
The key feature in JSONB is that most internal pointers [from arrays and objects] to values are in the form of lengths, with every 32nd pointer being an offset. This comes from the observation that offsets will not repeat, therefore are difficult to compress w/ off the shelf compression algorithms, but length values will often be the same and thus be compressible. This means that iterating an array (say) requires 31 additions for every 32 elements to recover the offsets to those 31 elements' values.
The story of how they came to this optimization for compression is fascinating. IIRC they implemented an offsets-only JSONB and were very happy with it until they discovered that that form of JSONB did not compress anywhere near as well as expected, and since PG was close to shipping, a feverish hunt for the cause ensued that culminated in the fix of mostly-using-lengths-instead-of-offsets.
[1] https://www.oasis-open.org/committees/tc_home.php?wg_abbrev=...
Once you get the hang of it the syntax feels extremely powerful. The only other thing it reminded me of is the first time I learned enough SQL to be dangerous.
You really, really are going to want to check jq out at least a little if you want to improve the state of the art in this area. It has an excellent manual btw.
Edit: you ask about "metaformats" such as object per newline. jq handles this well too
The variant column is useful for "let's dump this data into this column and separate it out in the nextstep"
Snowflake comes with a couple of methods of parsing semi structured data like JSON, XML, and even "custom" formats
We are playing around with Snowflake at work right now and I quite like it
I know this is a big ask, but if DuckDB can be in lock step with Postgres on JSONB handling :french-chef-kiss:
As far as I am aware (haven't researched), the standard way to get logs or metadata out of AWS Cloudwatch is in a JSON lines [1] format. Therefore, even if I were to write a custom JSON output format for my custom service, I would try my best to stick to the JSON lines format since I would have already written parsers and data connectors for AWS Cloudwatch. That's just me though.
I am biased though as I wrote OjG to handle what other tools were not able to do.
Most of these systems strongly encourage or outright enforce JSONL, so that’s the defacto standard, and most tooling or pipelines are going to generate that nowadays.
You can obviously still have a row of arrays, and different systems have slightly different approaches on how to deal with those. In Spark, this is referred to as “exploding”, in Presto you would cross join to unnest an array, in Redshift you can glob on the super type.
I’m not sure I have a particular favorite, only that the database support such an operation since it is a common occurrence.
As to JSON metaformat (I assume you mean for ingestion), a format of an array of objects works best.
For the last question, the main gap I find is trying to understand the 'structure' of json data that I didn't generate directly. There aren't great tools to show whether all the JSON records have the same nested structure with similar key names, etc.
What we should do is teach jq to read and write various binary JSON formats including JSONB.
* Which dialect for querying worked the best for you?
* Which tools do you enjoy the most? This could be for any reason: convenience of install/availability, expressivity, raw speed, other niche features, etc.
* What sort of JSON "meta-formats" are the most important/common for you? E.g. in a file you could have object-per-line, object-of-arrays, array-of-objects, or in an SQL context you could have object-per-row or object-of-arrays-as-table, etc). I'd love to hear about others that are important to you.
* What do you feel is the biggest gap/missing feature in currently available tools?