5 ms·

Nice find. I think I'll try it out. Although I was hoping for a real SQL type experience. I don't understand why no one just copies SQL so I can write a query l

by jjeaff 3y ago

Nice find. I think I'll try it out. Although I was hoping for a real SQL type experience. I don't understand why no one just copies SQL so I can write a query like "SELECT * FROM $json WHERE x>1". Everyone seems to want to invent their own new esoteric symbolic query language as if everything they do is a game of code golf. I really wish everyone would move away from this old Unix mentality of extremely concise, yet not-self-evident syntax and do more like the power shell way.

soulbadguy 3y ago
While i agree about the general sentiment on preferring well defined and explicit standard as opposed to "cute" custom made languages. In this case i am not convince that SQL would be the best candidate for querying nested structures like JSON.Something like xpath maybe.
- jjeaff 3y ago
  I agree, it wouldn't be the best to handle all json edge cases, but it would be a super easy way to quickly get data from a big chunk of simple json and you could just use subqueries or query chaining for nested results. For anyone who hasn't used powershell, this is the difference I'm talking about. I would not be able to write either of these without looking up the syntax. But knowing very little about powershell, I can tell exactly what that command means while the bash command, not so much. ```powershell $json | ConvertFrom-Json | Select-Object -ExpandProperty x ``` ```bash echo $json | jq '.x' ```
  - deredede 3y ago
    On the other hand, I find the bash one clear and concise. That PowerShell example is so verbose, it'd drive me crazy to do any sort of complex manipulation this way! To each their own, I guess.
    - jjeaff 3y ago
      If all I was doing is writing code, I agree. But like most developers, I think I read a lot more code than I write.
- deleted 3y ago
  [deleted]
- tubthumper8 3y ago
  JSONPath? https://datatracker.ietf.org/doc/id/draft-goessner-dispatch-jsonpath-00.html https://datatracker.ietf.org/doc/id/draft-goessner-dispatch-...
justinsaccount 3y ago
The datafusion cli https://arrow.apache.org/datafusion/user-guide/cli.html https://arrow.apache.org/datafusion/user-guide/cli.html can run SQL queries against existing json files.
filmor 3y ago
SQL is built for relational/tabular data, JSON is not relational and usually not tabular.
- im3w1l 3y ago
  Well there is nothing saying you can't put relational data in json format.
  - stevage 3y ago
    But that wouldn't help query arbitrary JSON files which was the point.
pdntspa 3y ago
Be the change you want to see. I personally don't understand why people aren't willing to learn instead. It's not hard to sit down and pick up a new skill and it's good to step out of one's comfort zone. I personally hate Powershell syntax, brevity is the soul of wit and PS could learn a thing or two from bash and "the linux way". We seem obsessed with molding the machine to our individual preferences. Perhaps we should obsess over the opposite: molding our mind to think more like the machine. This keeps a lot of things simple, uncomplicated, and flexible. Does a painter wish for paints that were more like how he wanted them to be? Sure, but at the end of the day he buys the same paint everyone else does and learns to work with his medium.
- unsui 3y ago
  While I appreciate the sentiment for bending your mind, rather than the spoon, the practical reality is that developer time is far costlier than compute time. It is easier to map compute structures and syntax to existing mental models than to formulate new mental models. The latter is effortful and time-consuming. So, given the tradeoffs, I could learn a new language, or leverage an existing language to get things done. And yes, given sufficient resources (particularly time), developing new mental models is ideal, but reality often prohibits the ideal.
  - 3np 3y ago
    If the crux is that you want something that maps closer to your personal mental model than what's available, I guess the other option is to build the missing tool yourself. That's the other side of "be the change you want to see". > So, given the tradeoffs, I could learn a new language, or leverage an existing language to get things done. There is also the option to create a new language (jqsql or whatnot), optionally sharing it publically. If you do this I think you'd find out why beyond very trivial stuff, sibling commenters have a point in that SQL isn't a good fit for nested data like JSON. Would still be a useful exercise!
- stevage 3y ago
  In my case, my memory doesn't work that way. I have learnt jq several times but I don't use it frequently enough to retain the knowledge. A better tool for me would be something that uses JS syntax but with some syntactic sugar and a great man page.
screature2 3y ago
I think the closest I've seen to a SQL experience for JSON is how steampipe stores json columns as jsonb datatypes and allows you to query those columns w/postgres JSON functions etc. - https://steampipe.io/docs/sql/querying-json#querying-json https://steampipe.io/docs/sql/querying-json#querying-json #example w/the AWS steampipe plugin (I think this is a wrapper around the AWS go SDK) - https://hub.steampipe.io/plugins/turbot/config https://hub.steampipe.io/plugins/turbot/config #I think this lets you query random json files. (edited to try to fix the bulleting)
bobobar339 3y ago
DuckDB does just this, https://duckdb.org/docs/archive/0.9.2/guides/import/json_import https://duckdb.org/docs/archive/0.9.2/guides/import/json_imp...
pgeorgi 3y ago
> Although I was hoping for a real SQL type experience. I don't understand why no one just copies SQL so I can write a query like "SELECT * FROM $json WHERE x>1". With somewhat tabular data, you can use sqlite to read the data into tables and then work from there. Example 10 from https://opensource.adobe.com/Spry/samples/data_region/JSONDataSetSample.html https://opensource.adobe.com/Spry/samples/data_region/JSONDa... (slightly fixed by removing the ellipsis) results in this interaction: sqlite> select json_extract(value, '$.id'), json_extract(value, '$.type') from json_each(readfile('test.json'), '$.items.item[0].batters.batter'); 1001|Regular 1002|Chocolate 1003|Blueberry 1004|Devil's Food sqlite> select json_extract(value, '$.id'), json_extract(value, '$.type') from json_each(readfile('test.json'), '$.items.item[0].topping'); 5001|None 5002|Glazed 5005|Sugar 5007|Powdered Sugar 5006|Chocolate with Sprinkles 5003|Chocolate 5004|Maple Instead of "select" this could also flow into freshly created tables using "insert into" for more complex scenarios.
throwaway2037 3y ago
do more like the power shell way I just checked the GitHub page [1] for Microsoft PowerShell. It looks written in C# and available on Win32/MacOS/Linux, where DotNet is now supported. Do you use PowerShell only on Win32 or other platforms also? Everyone seems to want to invent their own new esoteric symbolic query language Can you give an example of something that PS can do that is built-in for text processing, instead of a proprietary symbolic query language? [1] https://github.com/PowerShell/PowerShell https://github.com/PowerShell/PowerShell
- jjeaff 3y ago
  By "the powershell way" I don't mean actually using powershell. I just mean using verbose, descriptive commands that one can easily understand what it does without having a working knowledge of the scripting language.
chthonicdaemon 3y ago
Have you looked at [duckdb's JSON support](https://duckdb.org/docs/extensions/json.html https://duckdb.org/docs/extensions/json.html)? It's pretty transparent and you can do exactly what you say: `select * from 'file.json' where x > 1` will work with "simple" json files like {"x": 1, "y": 2} and [{"x": 1, "y":2}, {"x":2, "y":3}]
psd1 3y ago
nushell and pwsh. I'm not familiar with nushell, but pwsh offers where, select, foreach, group, sort. N.B. those aliases are not created by default on *nix It's pipeline-based and procedural, but you can be very declarative in data processing
nbk_2000 3y ago
OctoSQL[1] does a pretty good job of allowing you to query JSON (and CSV) with SQL. [1] https://github.com/cube2222/octosql https://github.com/cube2222/octosql
cryptonector 3y ago
> I don't understand why no one just copies SQL so I can write a query like "SELECT * FROM $json WHERE x>1". You could ask the same with respect to XML too -- why XPath/XSLT instead of SQL? The problem is that SQL isn't that convenient when you're querying data in a free-form and recursive schema. Especially the latter, because recursive queries in SQL are just not pithy. I say this as someone who loves SQL.

by jjeaff 3y ago

soulbadguy 3y ago

While i agree about the general sentiment on preferring well defined and explicit standard as opposed to "cute" custom made languages. In this case i am not convince that SQL would be the best candidate for querying nested structures like JSON.Something like xpath maybe.

jjeaff 3y ago
I agree, it wouldn't be the best to handle all json edge cases, but it would be a super easy way to quickly get data from a big chunk of simple json and you could just use subqueries or query chaining for nested results. For anyone who hasn't used powershell, this is the difference I'm talking about. I would not be able to write either of these without looking up the syntax. But knowing very little about powershell, I can tell exactly what that command means while the bash command, not so much. ```powershell $json | ConvertFrom-Json | Select-Object -ExpandProperty x ``` ```bash echo $json | jq '.x' ```
- deredede 3y ago
  On the other hand, I find the bash one clear and concise. That PowerShell example is so verbose, it'd drive me crazy to do any sort of complex manipulation this way! To each their own, I guess.
  - jjeaff 3y ago
    If all I was doing is writing code, I agree. But like most developers, I think I read a lot more code than I write.
deleted 3y ago
[deleted]
tubthumper8 3y ago
JSONPath? https://datatracker.ietf.org/doc/id/draft-goessner-dispatch-jsonpath-00.html https://datatracker.ietf.org/doc/id/draft-goessner-dispatch-...

justinsaccount 3y ago

The datafusion cli https://arrow.apache.org/datafusion/user-guide/cli.html https://arrow.apache.org/datafusion/user-guide/cli.html can run SQL queries against existing json files.

filmor 3y ago

SQL is built for relational/tabular data, JSON is not relational and usually not tabular.

im3w1l 3y ago
Well there is nothing saying you can't put relational data in json format.
- stevage 3y ago
  But that wouldn't help query arbitrary JSON files which was the point.

pdntspa 3y ago

Be the change you want to see. I personally don't understand why people aren't willing to learn instead. It's not hard to sit down and pick up a new skill and it's good to step out of one's comfort zone. I personally hate Powershell syntax, brevity is the soul of wit and PS could learn a thing or two from bash and "the linux way". We seem obsessed with molding the machine to our individual preferences. Perhaps we should obsess over the opposite: molding our mind to think more like the machine. This keeps a lot of things simple, uncomplicated, and flexible. Does a painter wish for paints that were more like how he wanted them to be? Sure, but at the end of the day he buys the same paint everyone else does and learns to work with his medium.

unsui 3y ago
While I appreciate the sentiment for bending your mind, rather than the spoon, the practical reality is that developer time is far costlier than compute time. It is easier to map compute structures and syntax to existing mental models than to formulate new mental models. The latter is effortful and time-consuming. So, given the tradeoffs, I could learn a new language, or leverage an existing language to get things done. And yes, given sufficient resources (particularly time), developing new mental models is ideal, but reality often prohibits the ideal.
- 3np 3y ago
  If the crux is that you want something that maps closer to your personal mental model than what's available, I guess the other option is to build the missing tool yourself. That's the other side of "be the change you want to see". > So, given the tradeoffs, I could learn a new language, or leverage an existing language to get things done. There is also the option to create a new language (jqsql or whatnot), optionally sharing it publically. If you do this I think you'd find out why beyond very trivial stuff, sibling commenters have a point in that SQL isn't a good fit for nested data like JSON. Would still be a useful exercise!
stevage 3y ago
In my case, my memory doesn't work that way. I have learnt jq several times but I don't use it frequently enough to retain the knowledge. A better tool for me would be something that uses JS syntax but with some syntactic sugar and a great man page.

screature2 3y ago

I think the closest I've seen to a SQL experience for JSON is how steampipe stores json columns as jsonb datatypes and allows you to query those columns w/postgres JSON functions etc. - https://steampipe.io/docs/sql/querying-json#querying-json https://steampipe.io/docs/sql/querying-json#querying-json #example w/the AWS steampipe plugin (I think this is a wrapper around the AWS go SDK) - https://hub.steampipe.io/plugins/turbot/config https://hub.steampipe.io/plugins/turbot/config #I think this lets you query random json files. (edited to try to fix the bulleting)

bobobar339 3y ago

DuckDB does just this, https://duckdb.org/docs/archive/0.9.2/guides/import/json_import https://duckdb.org/docs/archive/0.9.2/guides/import/json_imp...

pgeorgi 3y ago

> Although I was hoping for a real SQL type experience. I don't understand why no one just copies SQL so I can write a query like "SELECT * FROM $json WHERE x>1". With somewhat tabular data, you can use sqlite to read the data into tables and then work from there. Example 10 from https://opensource.adobe.com/Spry/samples/data_region/JSONDataSetSample.html https://opensource.adobe.com/Spry/samples/data_region/JSONDa... (slightly fixed by removing the ellipsis) results in this interaction: sqlite> select json_extract(value, '$.id'), json_extract(value, '$.type') from json_each(readfile('test.json'), '$.items.item[0].batters.batter'); 1001|Regular 1002|Chocolate 1003|Blueberry 1004|Devil's Food sqlite> select json_extract(value, '$.id'), json_extract(value, '$.type') from json_each(readfile('test.json'), '$.items.item[0].topping'); 5001|None 5002|Glazed 5005|Sugar 5007|Powdered Sugar 5006|Chocolate with Sprinkles 5003|Chocolate 5004|Maple Instead of "select" this could also flow into freshly created tables using "insert into" for more complex scenarios.

throwaway2037 3y ago

do more like the power shell way I just checked the GitHub page [1] for Microsoft PowerShell. It looks written in C# and available on Win32/MacOS/Linux, where DotNet is now supported. Do you use PowerShell only on Win32 or other platforms also? Everyone seems to want to invent their own new esoteric symbolic query language Can you give an example of something that PS can do that is built-in for text processing, instead of a proprietary symbolic query language? [1] https://github.com/PowerShell/PowerShell https://github.com/PowerShell/PowerShell

jjeaff 3y ago
By "the powershell way" I don't mean actually using powershell. I just mean using verbose, descriptive commands that one can easily understand what it does without having a working knowledge of the scripting language.

chthonicdaemon 3y ago

Have you looked at [duckdb's JSON support](https://duckdb.org/docs/extensions/json.html https://duckdb.org/docs/extensions/json.html)? It's pretty transparent and you can do exactly what you say: `select * from 'file.json' where x > 1` will work with "simple" json files like {"x": 1, "y": 2} and [{"x": 1, "y":2}, {"x":2, "y":3}]

psd1 3y ago

nushell and pwsh. I'm not familiar with nushell, but pwsh offers where, select, foreach, group, sort. N.B. those aliases are not created by default on *nix It's pipeline-based and procedural, but you can be very declarative in data processing

nbk_2000 3y ago

OctoSQL[1] does a pretty good job of allowing you to query JSON (and CSV) with SQL. [1] https://github.com/cube2222/octosql https://github.com/cube2222/octosql

cryptonector 3y ago

> I don't understand why no one just copies SQL so I can write a query like "SELECT * FROM $json WHERE x>1". You could ask the same with respect to XML too -- why XPath/XSLT instead of SQL? The problem is that SQL isn't that convenient when you're querying data in a free-form and recursive schema. Especially the latter, because recursive queries in SQL are just not pithy. I say this as someone who loves SQL.