[ANN] ob-dsq.el: Use SQL to join and query JSON, CSV, Org tables, all the things!

[dsq](https://github.com/multiprocessio/dsq) by [Multiprocess Labs](https://multiprocess.io) is a command line tool that allows for running SQL queries against multiple files of a growing number of supported types (JSON, CSV, many more) as database tables and for joining between them. Spending half my day in Org mode, I created [ob-dsq.el](https://github.com/fritzgrabo/ob-dsq), which adds Org tables, Org source block results, and basically any valid Org reference to the list of supported input types. I wrote a quick [blog post](https://fritzgrabo.com/posts/introducing-ob-dsq/) to demo the package's basic usage. Its repository includes [a bunch of examples](https://github.com/fritzgrabo/ob-dsq/tree/main/examples) showcasing all features in detail. Shoutout to Multiprocess Labs for this awesome tool!

This is really amazing!!

Thanks for the kind words; I'm glad you like it.

Oh that rules! I'm glad someone had this idea, I never heard of dsq, it would save me a lot of interface code. Are there any plans to add entry properties (ie. what's in the PROPERTIES drawer) as an input type? That would be the cinch for me.

Thanks! Re. the use of properties as an input type -- could you give a quick example of what that would look like, please? Did you mean to query the actual set of properties as a data source, or to assign a single property's value to a [variable for expansion](https://github.com/fritzgrabo/ob-dsq/tree/main/examples#variable-expansion)?

The former, querying properties as a data source. I haven't looked super thoroughly at dsq so I'm not sure if the layout of the data (bunch of arbitrary headers) would be a poor fit, but a utility to do something like "select IA_TIMESTAMP from [all headers] where [...]" would make my day. Even moreso if there were matching UPDATE, EXISTS, etc. type verbs/adjectives but I get the impression that's outside dsq's wheelhouse :) (org-entry-get) is the stock function for what I'm asking for, its doc may be clearer.

Ah, I see now, thanks for the clarification. I think your use case might be better served by alphapapa's [org-ql](https://github.com/alphapapa/org-ql) package. It's not using SQL, but it does allow for querying the actual Org file's *outline structure*, including values of properties (check out the "properties" entry in the "[General predicates](https://github.com/alphapapa/org-ql#general-predicates)" section of the package's README for details). In contrast, ob-dsq helps with passing *tabular data* from Org tables, Org source block results etc. to dsq for querying. Hope that helps!

I already use org-ql heavily, that's why I zeroed in on this. :) I will say thank you for linking directly to general predicates though, for the next person's sake, figuring that system out gave me a headache. It's a very powerful package, power tends to come with abstrusity. I suppose in my mind this use of headers is tabular -- rows with the header itself as the key and the properties as columns. But like I said and you corroborate, it's an awkward fit. And at any rate, with your code I can just as well write the shim myself at some point if I get the unction. So I must thank you again for writing it and bringing to my attention.

>I suppose in my mind this use of headers is tabular -- rows with the header itself as the key and the properties as columns. Got it. Well, if you don't mind writing a little bit of elisp, as you said, then you could indeed use org-ql (or any other means) to transform the structure of one or more Org files into tabular data, then feed that into ob-dsq for querying via SQL. That would allow for a "select IA\_TIMESTAMP from \[all headers\] where \[...\]" type of query that you mentioned in your first comment. Here's a quick example using org-ql and ob-dsq: * Org-QL first :PROPERTIES: :foo: first :bar: one :END: * Org-QL second :PROPERTIES: :foo: second :bar: two :END: * Org-QL third :PROPERTIES: :foo: third :bar: three :END: * Example #+name: org-ql #+begin_src elisp (cons (list "heading" "foo" "bar") (mapcar (lambda (item) (let* ((pom (plist-get (cadr item) :begin)) (heading (org-entry-get pom "ITEM")) (foo (org-entry-get pom "foo")) (bar (org-entry-get pom "bar"))) (list heading foo bar))) (org-ql-select (current-buffer) '(regexp "Org-QL")))) #+end_src #+begin_src dsq :input org-ql SELECT heading, bar FROM {} WHERE foo IN ("first", "third") #+end_src #+RESULTS: | heading | bar | |--------------+-------| | Org-QL first | one | | Org-QL third | three |

Oh good, you already un-mangled it. I'm going to tuck it away for reference for when I'm of a mind to tinker with it. Can already tell it will be very useful to have an approach filtered through the mind of someone who already understands the software.