Dasel: Select, put and delete data from JSON, TOML, YAML, XML and CSV

DuckDB is a good example of a (literally) serverless SQL-based tool for data processing. It is designed to be able to treat the common data serialization formats as though they are tables in a schema [1], and you can export to many of the same formats. With extensions, you can also connect to relational databases as foreign tables.

This connectivity is a big reason it has built a pretty avid following in the data science world.

[1] https://duckdb.org/docs/data/overview

[2] https://duckdb.org/docs/extensions/json#json-importexport

[3] https://duckdb.org/docs/extensions/postgres

Is your SQL Turing-complete? If yes, then it could query anything. Whether or not you'd like the experience is another thing.

Queries are programs. Querying data from a fixed schema, is easy. Hell, you could make an "universal query language" by just concatenating together this dasel, with SQL and Cypher, so you'd use the relevant facet when querying a specific data source. The real problem starts when your query structure isn't fixed - where what data you need depends on what the data says. When you're dealing with indirection. Once you start doing joins or conditionals or `foo[bar['baz']] if bar.hasProperty('baz') else 42` kind of indirection, you quickly land in the Turing tarpit[0] - whatever your query language is, some shapes of data will be super painful for it to deal with. Painful, but still possible.

--

[0] - https://en.wikipedia.org/wiki/Turing_tarpit

Sure there could be -- any turing-complete language (which SQL is) can query anything.

But the reason we have different programming languages* is because they have different affordances and make it easy to express certain things at the cost of being less convenient for other things. Thus APL/Prolog/Lisp/C/Python can all coexist.

SQL is great for relational databases, but it's like commuting to work in a tank when it comes to key-value stores.

* and of course because programmers love building tools, and a language is the ultimate tool.

> Or put another way, is there any data storage format that couldn’t be queried by SQL?

We created PLDB.io (a Programming Language DataBase) and have studied nearly every language ever created and thought about this question a lot.

Yes, there could be 1 language to query everything, but there will always be a better DSL more relevant for particular kinds of data than others. It's sort of like how with a magnifying glass you can magnify anything, but if you want to look at bacteria you're going to want a microscope (and you wouldn't want a microscope to study an elephant).

Now it may turn out that there is 1 universal syntax that works best for everything (I'm sure people can guess what I would say), but I can't think of a case where you wouldn't want to have a DSL with semantics evolved to match a particular domain.

Off the top of my head, SQL can't do lists as values, and doesn't have simple key-value storage. Json doesn't have tables, or primary keys / foreign keys, and can have nested data

Depends on how keen you are on pure SQL. For example, postgres and sqlite have json-extensions, but they also enhance the syntax for it. Simliar can be done for all other formats too, but this means you need to learn special syntax and be aware of the storage-format for every query. This is far off from a real universal language.

I suspect for most data structures you could construct an index to make querying faster. But think about querying something like a linked list: it is not going to be too efficient without an index but you should still be able to write an engine that will do so.

If you have something like a collection of arbitrary JSON objects without a set structure you should still be able to express what you are trying to do with SQL because Turing completeness means it can examine the object structure as well as contents before deciding what to do with it. But your SQL would look more like procedural code than you might be used to.

Even if SQL and/or another query language could be Turing-complete, that doesn't mean that you can have 1 universal language to perform all possible queries in an efficient way. In basic computer science terms that means that your data structure is linked with the queries, and efficiency you want to achieve, and ad-hoc changes should be created for specific problems.

  User | Telephone Numbers
  -----+------------------
  A    | 123, 456           <- not atomic; more than 1 number (i.e. a set)
  B    | 789

In general you don't want a universal query language. Depending on the shape of the data you want different things to be easily expressible. You can, for example express queries on tree-shaped data with SQL (see xPath-Accelerator), but it is quite cumbersome and its meaning is lost to the reader. I.e.: It's fine when computer-generated, but there is too much noise for a human to read/write themselves. I'd be glad to be proven wrong here, but as time has shown, there is no one size fits all for programming languages. The requirements for different applications just vary too much.

I would really like to find a good workflow for idempotent modifications to INI files, but haven't stumbled across one yet.

It is a bit perl-ish, but being pure and functional it is a little easier to reason about when you have to revisit your queries.

PS I am certainly bookmarking your tool as well =]

There is also amusing project jqjq that implements jq in jq itself that I love to point folks at to show how expressive the language is: https://github.com/wader/jqjq

I’m obviously going to biased here, but it’s definitely worth your time checking out some alt shells.

It looks really well done, I think I'm just failing to see how this is more beneficial than just opening a single file in the editor and making changes, or writing a quick functional script so you have the history of the changes that were made to a batch of files.

If someone could explain how I could (and why I should) add a new tool to my digital toolbelt, I'd greatly appreciate it.

XPath, although it had some clunky artifacts for XML (which was the reason we moved from XML like namespaces... ugh), had basically the apex of expression/path/navigation capabilites. It would be really nice to see XPath ported to a general nav language that is supported by all programming environments and handled all the relevant formats.

I wrote about this a little in https://www.bbkane.com/blog/go-project-notes/#scripting-chan... and it's really helped me keepy GitHub workflows and various config files in sync across project repos

That's why wheb working with Json I Love Jsonata.org

And it's not surprise is that good the creator of jsonata is on the xpath and xquery commites.

I have tried jq, jmespath, yq and Jsonata was the best for me.

https://github.com/sharkdp/hyperfine

Dasel MIT License Copyright (c) 2020 Tom Wright

desel: .data.all().filterOr(moreThan(.quantity,3),equal(.quantity,3)).mapOf(key,key,quantity,quantity)

yq: .data[] | select(.quantity >= 3) | {"key": .key, "quantity": .quantity}

jq: .data[] | select(.quantity >= 3) | {key, quantity}