Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Sq.io: jq for databases and more

sq.io

Post reply on HN
Print view View original

21–30 of 134 posts

Re: Sq.io: jq for databases and more

#21

Post by kwailo »

jasongill wrote: post #18

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)

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...

Re: Sq.io: jq for databases and more

#22

Post by nbk_2000 »

jasongill wrote: post #18

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)

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

https://github.com/cube2222/octosql

Re: Sq.io: jq for databases and more

#24

Post by zie »

jasongill wrote: post #18

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)

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.

Re: Sq.io: jq for databases and more

#27

Post by danielhep »

minikomi wrote: post #19
jasongill wrote: post #18

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)

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.

Re: Sq.io: jq for databases and more

#28

Post by masklinn »

zie wrote: post #24
jasongill wrote: post #18

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)

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.

Re: Sq.io: jq for databases and more

#29

Post by AlphaSite »

rout39574 wrote: post #11

I love JQ. But ... I'd never considered its query language to be particularly admirable. If I want to ask questions of some databases, I don't understand why I'd choose JQ's XPATH-like language to do it.

I think for certain types of data manipulation and querying it’s notable more succinct, sql with CTEs is a little better but still far more verbose than data piping.

Re: Sq.io: jq for databases and more

#30

Post by aurareturn »

varenc wrote: post #14
candiddevmike wrote: post #4

This is neat but I'm not really seeing anything I can't do with standard SQL and CLI tools like psql. Seems like you'd learn more reusable things using standard SQL too.

I find sq handy when you use it to accomplish things you can't (easily) do with just raw SQL. Things like: exporting certain rows to JSON or CSV, transforming rows into nicely formatted log lines for viewing, or reading in a CSV file and querying it the same way you'd query other databases. It's particularly easy to start using if you're already familiar with the jq. If you use things like `array_to_json(array_agg(ro…

I agree with @candiddevmike. Might I add that you can do those things fairly easily now with ChatGPT/Claude. I doubt LLMs know how to use Sq.io that much.
Post reply on HN