5 ms·

Allow me a moment to hijack this thread. I'm a PhD student looking to make analytics of JSON data a first-class citizen in https://duckdb.org/ https://duckdb.or

• sandGorgon 5y ago
  Store the json in postgresql as JSONB. 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.
  • PaulHoule 5y ago
    arangodb for the win. I've done data analytics in it and OLTP. You never hear hype about arangodb because arangodb users see it as a "secret weapon" and want their competitors to use mongo.
    • The_rationalist 5y ago
  • boredumb 5y ago
    I can second that using JSONB with postgres is fast and easy, the features and operators to query json structures are also about as extensive as you'll ever need.
    • gavinray 5y ago
      Thirded, though DuckDB is compelling for scenarios when you'd traditionally be able to use SQLite, if analytics/OLAP is your thing. It's exciting and brilliant technology. I believe the authors are the same ones behind MonetDB.
  • orlp 5y ago
    > Not trying to flame 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.
    • anakaine 5y ago
      Probably best not to encourage fighting, since flaming is what an argument descends into when it starts to get directed and nasty. How about "discuss away", heh.
  • bachmeier 5y ago
    Wouldn't that require setting up and running a postgres server? DuckDB is an sqlite replacement.
    • cryptonector 5y ago
      You could copy the JSONB code and not run PG.
      • bigcat123 5y ago
• infinite8s 5y ago
  I have worked with JSON in SQLServer, and I agree with the sibling comment that JSONB is the ideal storage format. SLQServer's JSON querying functions are pretty straightforward, although they do expect that you know the schema of the json data you are querying. 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.
• ellimilial 5y ago
  If it fits on a single machine - jq, flat files, JSON lines / avro if relatively flat. Change to a tabular format if when nesting not required. 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).
  • galdosdi 5y ago
    I cannot describe how much I love jq. Best new (to me) tool I discovered in all of 2020. 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
  • cryptonector 5y ago
    JSONB is incredibly awesome, and should be extracted from PG and made usable on its own. 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.
    • breatheoften 5y ago
• jimsparkman 5y ago
  Since duckdb is OLAP, it would directly compare with other columnar database technologies like Redshift, Presto/Athena, etc. 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.
• carlineng 5y ago
  Snowflake’s handling of JSON data via the “variant” data type is quite nice to work with, both in terms of speed and convenience of the access methods.
  • geoduck14 5y ago
    I'll add to this: 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
• dorianmariefr 5y ago
  jq for quick lookup, jsonb for actual storage, indexing jsonb is a pain though
  • cryptonector 5y ago
    JSONB is optimized for compression, but also pessimized for writing. What we should do is teach jq to read and write various binary JSON formats including JSONB.
• gavinray 5y ago
  > * 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 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 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 =(
• iknownothow 5y ago
  I decided not to introduce DuckDB for my org, 6 team members and <10TB of data solely based on lack of full support for Postgres like JSONB. Currently, we are happy using Postgresql JSONB with a small performance penalty . I would be loathe to introduce a tool which requires a new syntax for handling JSONs. 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. [1] https://jsonlines.org/ https://jsonlines.org/
• jamal-kumar 5y ago
  I've written programs for digesting, parsing, and querying data out of many gigabytes of JSON REALLY REALLY fast by not doing any unnecessary memory allocation at all. Keeping everything into streaming APIs and out of that particular bottleneck is the key to doing this, really just no malloc, and that's what this article seems to be about kind of... When you manage to avoid that you can do it in pretty much any language. AWK/SED is perfect for when it comes to field-seperated data like CSV, these tricks are really old! When it comes to JSON I've just written my own stuff, though, because that nested-tree-structure is bound to be different for each data set.
• dimatura 5y ago
  I find myself analyzing a piles of json files, or large json files now and then, usually for one-off or infrequent analyses. If I'll never touch this data again, then usually the first thing I do is collect all the data and do whatever is needed to put it in a pandas dataframe, and then throw away the original data. Thankfully I haven't had an issue with the RAM required for this in the last few years. (working with 64GB ram). 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.
• klabb3 5y ago
  > * What sort of JSON "meta-formats" are the most important/common for you? E.g. in a file you could have object-per-line, [...] 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.
• peterohler 5y ago
  For me OjG (https://github.com/ohler55/ojg https://github.com/ohler55/ojg) has been great. I regularly use it on files that can not be loaded into memory. The best JSON file format for multiple record is one JSON document per record all in the same file. OjG doesn't care if they are on different lines. It is fast (https://github.com/ohler55/compare-go-json https://github.com/ohler55/compare-go-json) and uses a fairly complete JSONPath implementation for searches. Similar to jq but using JSONPath instead of a proprietary query language. I am biased though as I wrote OjG to handle what other tools were not able to do.
  • dmoura 5y ago
    Are you able to calculate aggregates, like an average?
• zX41ZdbW 5y ago
  That's great, but you don't have to finish your PhD. The task is already solved in ClickHouse: https://colab.research.google.com/drive/1pGmfpNdGyzE1KEXDnSSiRgxQy9bcinGZ?usp=sharing https://colab.research.google.com/drive/1pGmfpNdGyzE1KEXDnSS...