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/. I'm still very much in a literature study stage, so I'd love to hear your success (or failure) stories about analyzing data stored in JSON. E.g. I'd love to hear about:

* 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?

  > * 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 =(