Hacker News Reader

Comment by jasongill | Hacker News Reader

This is interesting. I wonder if there is anything that does the opposite - takes JSON input and allows you to query it with SQL syntax (which would be more appealing to an old-timer like me)

View on HN

This tool can do that:

    $ cat example.json | sq sql 'SELECT * FROM data'
    actor_id  first_name  last_name  last_update
    1         PENELOPE    GUINESS    2020-06-11T02:50:54Z
    2         NICK        WAHLBERG   2020-06-11T02:50:54Z

One of the data source types is 'json' and the command 'sq sql ...' lets you execute SQL directly instead of using their default query language (SLQ).

wow, I missed that in the docs but this is exactly what I was thinking!

duckdb!

https://duckdb.org/docs/extensions/json.html

I'm using DuckDB to parse GTFS data, which comes in a CSV format. It works wonderfully.

clickhouse-local is incredible, and, in addition to JSON, support TSV, CSV, Parquet and many other input formats. See https://clickhouse.com/blog/extracting-converting-querying-l...

Why is this better than DuckDB?

Why is DuckDB better than clickhouse-local?

duckdb is shorter to type than clickhouse-local and at the command line brevity is king! Of course the winner here is chdb! (And don't talk to me about shell aliases) :-p

While on the topic, how exactly does chdb relate to clickhouse-local?

In PostgreSQL, you can just select against JSON and query away to your hearts content. They have JSON data types and functions to work on it.

That’s pretty much just jq though, you have bespoke json querying capabilities in sql but you’re not sql-querying your json.

At least not before postgres 17, in the latter json_table does provide this capability.

Thankfully PG17 is released and ready for production querying needs!

Octosql does this as well as a few other formats. I've found it useful several times.

https://github.com/cube2222/octosql

This question has come up a few times in this thread. However I don't see how people expect this to be possible unless they are talking about ndjson with flat records. JSON in general is a very nested format so languages based on relational algebra/calculus like SQL and PRQL are not going to be that useful unless the data is flattened and normalised first.

https://github.com/postgrespro/jsquery

Postgres:) load It into a jsonb column and the possibilities are endless, including indexing

let me introduce you to this hot, open source, nosql database that's webscale...

Reply on news.ycombinator.com