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

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.

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' ```

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.

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.

JSONPath?

https://datatracker.ietf.org/doc/id/draft-goessner-dispatch-...

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 #example w/the AWS steampipe plugin (I think this is a wrapper around the AWS go SDK)

- https://hub.steampipe.io/plugins/turbot/config #I think this lets you query random json files.

(edited to try to fix the bulleting)

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

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.

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.

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!

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.

What is "JS syntax"? And can you write a frontend for jq that converts "JS syntax" to jq syntax?

And is the jq man page poor? I'm sure they will accept patches for it.

The jq man page is pretty good IMO. It’s where/how I learned to use jq

I have that same problem, the advanced features I use too little to remember. Then I started working on a configuration language that should have a non-surprising syntax (json superset, mostly inspired by Python, Rust, Nix). And it turns out, this works well as a query language for querying json documents. https://github.com/ruuda/rcl Here is an example use case: https://fosstodon.org/@ruuda/111120049523534027

>I personally don't understand why people aren't willing to learn instead.

Mostly because if you don't use it that often then it ends up forgotten again. I can smash out plenty of trivial regexes, but anything even slightly complicated means I'm learning backreferences again for the 6th time in a decade.

> I personally don't understand why people aren't willing to learn instead

You misunderstand. As programmers we learn every day, obviously that's one of our strong points.

The real problem is that every single tool wants you to go deep and learn their particular dyslexic mini programming language syntax or advanced configuration options syntax. Why? We have TOML, we have SQL, we have a bunch of pretty proven syntaxes and languages that do the job very well.

A lot of these programmers authoring tools suffer from a severe protagonist syndrome which OK, it's their own personal character development to grapple with, but in the meantime us the working programmers are burning out because everyone and their dog wants us to learn their own brain child.

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

How so? Everything in "the machine" was created by other humans; from the latest CLI tool, to the CPU instruction set. As computer users, given that it's practically impossible for a single person to be familiar with all technologies, we must pick our battles and decide which technology to learn. Some of it is outdated, frustrating to use, poorly documented or maintained, and is just a waste of time and effort to learn.

Furthermore, as IT workers, it is part of our job to choose technologies worth our and our companies' time, and our literal livelihood depends on honing this skill.

So, yes, learning new tools is great, but there's only so much time in a day, and I'd rather spend it on things that matter. Even better, if no tool does what I want it to, I have the power to create a new one that does, and increase my development skills in the process.

brevity is not clarity.

“Brevity is the soul of wit”

Maybe we have different goals but I don’t get paid to write witty code and I don’t think anyone on my team would appreciate it if I did.

I don’t think the redeeming qualities of brevity in prose transfer to something like terse syntax.

The machine is uncomplicated and simple? That is the last way I would describe modern CPUs and their peripherals.

The whole point of programming is to bend the machine towards humans, not the other way around.

Yeah I don't understand why people aren't willing to learn SQL too.

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

Well there is nothing saying you can't put relational data in json format.

But that wouldn't help query arbitrary JSON files which was the point.

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

    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

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.

Have you looked at [duckdb's JSON support](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}]

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

OctoSQL[1] does a pretty good job of allowing you to query JSON (and CSV) with SQL.

[1] https://github.com/cube2222/octosql

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