4 ms·

While jq is a very powerful tool, I've also been using DuckDB a lot lately. SQL is a much more natural language if the data is somewhat tabular.

suchar 3y ago
Some time ago I tried Retool and it does have "Query JSON with SQL": https://docs.retool.com/queries/guides/sql/query-json https://docs.retool.com/queries/guides/sql/query-json (it is somewhat relevant because it was extremely convenient) It is somewhat similar to Linq in C# although SQL there is more standardised so I like it more. Also, it would be fantastic to have in-language support for querying raw collections with SQL. Even better: to be able to transparently store collections in Sqlite. It is always sad to see code which takes some data from db/whatever and then does simple processing using loops/stream api. SQL is much higher level and more concise language for these use cases than Java/Kotlin/Python/JavaScript
MrDrMcCoy 3y ago
I like textql [0] better for this use case, as it's simpler in my mind. [0] https://github.com/dinedal/textql https://github.com/dinedal/textql
- bdcravens 3y ago
  textql doesn't seem to work with JSON. I think the grandparent comment meant that the data was in a table of sorts, represented in JSON.
  - MrDrMcCoy 3y ago
    Ah, you're right. TextQL combined with Miller would be closer, but DuckDB can do the same things all in one. Always good to have a variety of tools to choose from.
CBLT 3y ago
I've found the same. I store all raw json output into a sqlite table, create virtual columns from it, then do a shell loop off of a select. Nested loops become unnested, and debugability is leagues better because I have the exact record in the db to examine and replay. I've noticed what I'm creating are DAGs, and that I'm constantly restarting it from the last-successfully-proccessed record. Is there a `Make`-like tool to represent this? Make doesn't have sql targets, but full-featured dag processors like Airflow are way too heavyweight to glue together shell snippets.
cryptonector 3y ago
Yes. SQL is much better for relational data with a strict schema. Though you'll still never get a way to express recursive queries in SQL w/o a lot of verbosity.

suchar 3y ago

Some time ago I tried Retool and it does have "Query JSON with SQL": https://docs.retool.com/queries/guides/sql/query-json https://docs.retool.com/queries/guides/sql/query-json (it is somewhat relevant because it was extremely convenient) It is somewhat similar to Linq in C# although SQL there is more standardised so I like it more. Also, it would be fantastic to have in-language support for querying raw collections with SQL. Even better: to be able to transparently store collections in Sqlite. It is always sad to see code which takes some data from db/whatever and then does simple processing using loops/stream api. SQL is much higher level and more concise language for these use cases than Java/Kotlin/Python/JavaScript

MrDrMcCoy 3y ago

I like textql [0] better for this use case, as it's simpler in my mind. [0] https://github.com/dinedal/textql https://github.com/dinedal/textql

bdcravens 3y ago
textql doesn't seem to work with JSON. I think the grandparent comment meant that the data was in a table of sorts, represented in JSON.
- MrDrMcCoy 3y ago
  Ah, you're right. TextQL combined with Miller would be closer, but DuckDB can do the same things all in one. Always good to have a variety of tools to choose from.

CBLT 3y ago

I've found the same. I store all raw json output into a sqlite table, create virtual columns from it, then do a shell loop off of a select. Nested loops become unnested, and debugability is leagues better because I have the exact record in the db to examine and replay. I've noticed what I'm creating are DAGs, and that I'm constantly restarting it from the last-successfully-proccessed record. Is there a `Make`-like tool to represent this? Make doesn't have sql targets, but full-featured dag processors like Airflow are way too heavyweight to glue together shell snippets.

cryptonector 3y ago

Yes. SQL is much better for relational data with a strict schema. Though you'll still never get a way to express recursive queries in SQL w/o a lot of verbosity.