dsq source code blocks use the custom :input header argument to specify
one or more data sources to query. Here’s a basic example that queries a
list of people in a file called people.json. Results are formatted as a
table with a header row by default (see below for other options).
#+begin_src dsq :input people.json
SELECT * FROM {}
#+end_src
#+RESULTS:
| id | name |
|----+---------|
| 1 | Alice |
| 2 | Bob |
| 3 | Charles |
The :input header argument accepts one or more data sources. Here’s an
example that runs a query across two input files with varying data types
(JSON and CSV).
#+begin_src dsq :input people.json languages.csv
SELECT people.name AS name, languages.name AS language
FROM {0} people
INNER JOIN {1} languages ON people.id = languages.person_id
#+end_src
#+RESULTS:
| name | language |
|-------+----------|
| Alice | Ruby |
| Alice | Elisp |
| Bob | Python |
Besides regular files, and similar to the :var header argument, the
:input header argument also accepts references to Org tables, Org
quotes, Org source blocks, etc. Here’s an example that runs a query on
an Org table named colors.
#+name: colors
| person_id | name |
|-----------+-------|
| 1 | Blue |
| 2 | Red |
| 2 | Green |
#+begin_src dsq :input colors
SELECT name FROM {}
#+end_src
#+RESULTS:
| name |
|-------|
| Blue |
| Red |
| Green |
References may also be located in other files.
#+begin_src dsq :input countries.org:countries
SELECT name FROM {}
#+end_src
#+RESULTS:
| name |
|-------------|
| Austria |
| New Zealand |
Both references and regular files can be used as data sources in a
single dsq source block. ob-dsq transparently writes the data of each
reference to a temporary file for dsq to query.
#+begin_src dsq :input people.json colors countries.org:countries
SELECT people.name AS name, colors.name AS color, countries.name AS country
FROM {0} people
INNER JOIN {1} colors ON people.id = colors.person_id
INNER JOIN {2} countries ON people.id = countries.person_id
#+end_src
#+RESULTS:
| name | color | country |
|-------+-------+-------------|
| Alice | Blue | Austria |
| Bob | Red | New Zealand |
| Bob | Green | New Zealand |
Here’s an example of a dsq source block querying the results of an Org
source block that downloads JSON data from an online API.
#+name: comments
#+begin_src shell :results verbatim
curl https://jsonplaceholder.typicode.com/comments
#+end_src
#+begin_src dsq :input comments
SELECT
email,
substr(name, 0, 32) AS name
FROM {}
WHERE email LIKE 'f%'
ORDER BY email
LIMIT 5
#+end_src
#+RESULTS:
| email | name |
|-------------------------------+---------------------------------|
| Fanny@danial.com | deleniti facere tempore et pers |
| Faustino.Keeling@morris.co.uk | rerum voluptate dolor |
| Felton_Huel@terrell.biz | ratione architecto in est volup |
| Ferne_Bogan@angus.info | dicta deserunt tempore |
| Francesco.Gleason@nella.us | doloribus quibusdam molestiae a |
This is where it get’s a little meta: it’s possible to pass Elisp forms
to the :input header argument, as long as they evaluate to a either a
single value that is a valid :input header argument, or a list of
values, each of which is either a valid :input header argument or
tabular data (that is, a list of lists).
Let’s unpack this step by step.
Here’s an example that queries the colors Org table from above:
#+begin_src dsq :input (concat "col" "ors")
SELECT name FROM {}
#+end_src
#+RESULTS:
| name |
|-------|
| Blue |
| Red |
| Green |
Here’s an Elisp form that evaluates to a list of data sources to query:
#+begin_src dsq :input `("people.json" ,(concat "col" "ors"))
SELECT people.name AS name, colors.name AS color
FROM {0} people
INNER JOIN {1} colors ON people.id = colors.person_id
#+end_src
#+RESULTS:
| name | color |
|-------+-------|
| Alice | Blue |
| Bob | Red |
| Bob | Green |
It’s also possible to either define tabular data to query or to call functions that generate such data on the fly. Consider this a shortcut to referencing an Org source block that defines or generates data.
Note that for this to work, the tabular data needs to be an element of a
wrapping list; it can’t be passed in as a :input header argument
directly, because the individual “rows” would be considered one data
source each, like in the examples above.
Here’s what that would look like for tabular data defined inline:
#+begin_src dsq :input '("people.json" (("person_id" "name") (1 "Blue") (2 "Red") (2 "Green")))
SELECT people.name AS name, colors.name AS color
FROM {0} people
INNER JOIN {1} colors ON people.id = colors.person_id
#+end_src
#+RESULTS:
| name | color |
|-------+-------|
| Alice | Blue |
| Bob | Red |
| Bob | Green |
And finally, let’s do an example that calls a function to generate the data to query on the fly.
Assume you have defined a simple org-extract function which uses the
fabulous org-ql package to fetch headlines from Org files for an org-ql
query and continues to extract their meta-data and custom properties as
tabular data:
(defun org-extract (files &optional query)
"Extract meta-data and custom properties for headings in FILES matching QUERY."
(let ((headlines (org-ql-select files query))
keywords)
;; collect unique property keywords
(mapcar (lambda (headline)
(cl-loop for (keyword . _value) on (cadr headline) by #'cddr
unless (member keyword keywords)
do (push keyword keywords)))
headlines)
(cons
;; header row: normalized column names
(mapcar (lambda (keyword)
(substring (downcase (symbol-name keyword)) 1))
keywords)
;; data rows
(mapcar (lambda (headline)
(mapcar (lambda (keyword)
(let ((value (plist-get (cadr headline) keyword)))
(if (or (stringp value) (numberp value))
value
(format "%s" value))))
keywords))
headlines))))Let’s sum up story points of tickets that are still “ready” to be worked
on in this week’s sprint.org (raw view) by assignee and component to
find out if we’d better reassess the ticket distribution among the team:
#+begin_src dsq :input `(,(org-extract "sprint.org" '(todo "READY")))
SELECT assignee, component, SUM("story-points") AS points FROM {} GROUP BY assignee, component
#+end_src
#+RESULTS:
| assignee | component | points |
|----------+-----------+--------|
| Fritz | Backend | 5 |
| Fritz | Frontend | 2 |
| Rainer | Frontend | 1 |
Queried objects in JSON data might contain “irregular” attributes. For
example, in an array of people objects, one object might specify a phone
attribute, while another does not. In the query result, all attributes
found in the queried data will be flattened out and null-ed where
needed.
#+begin_src dsq :input irregular.json
SELECT * FROM {}
#+end_src
#+RESULTS:
| id | name | newsletter | phone |
|----+-------+------------+----------------|
| 1 | Alice | false | nil |
| 2 | Bob | nil | 1-123-123-1234 |
Org supports a wide range of options to customize the formatting of the results of a source code block execution. Here are two quick examples; please see the docs for more details.
#+begin_src dsq :input people.json :results list
SELECT * FROM {}
#+end_src
#+RESULTS:
- ("id" "name")
- (1 "Alice")
- (2 "Bob")
- (3 "Charles")
#+begin_src dsq :input people.json :results verbatim code
SELECT * FROM {}
#+end_src
#+RESULTS:
#+begin_src dsq
[{"id":1,"name":"Alice"},
{"id":2,"name":"Bob"},
{"id":3,"name":"Charles"}]
#+end_src
Use :header yes (default) to include a header row in table results. Use
any other value to render the result without a header. This can be
useful for result types other than table (see below for other options).
#+begin_src dsq :input people.json :header no
SELECT * FROM {}
#+end_src
#+RESULTS:
| 1 | Alice |
| 2 | Bob |
| 3 | Charles |
Use :hlines yes to include horizontal lines between rows in table
results. Use any other value to render the result without horizontal
rows (default).
#+begin_src dsq :input people.json :hlines yes
SELECT * FROM {}
#+end_src
#+RESULTS:
| id | name |
|----+---------|
| 1 | Alice |
|----+---------|
| 2 | Bob |
|----+---------|
| 3 | Charles |
The rendering of null and false values can be customized using the
:null-value and :false-value header arguments.
#+begin_src dsq :input irregular.json :null-value "?" :false-value "nope"
SELECT * FROM {}
#+end_src
#+RESULTS:
| id | name | newsletter | phone |
|----+-------+------------+----------------|
| 1 | Alice | nope | ? |
| 2 | Bob | ? | 1-123-123-1234 |
Arguments can be passed to the source code block via :var FOO=BAR as
usual. dsq source code blocks replace occurrences of $FOO with BAR
before its evaluation.
Note that BAR can be a literal value or an Org reference.
Quoting and list concatenation might happen based on the type of BAR’s
value. Please see below for details.
Passing in a literal string value.
Note how the value Alice needs to be quoted manually in the query if
it’s used as a string value there: ob-dsq doesn’t assume that passed in
values are used as values in the query. In fact, you could use variable
expansion to pass in whole query clauses, subqueries, etc.
#+begin_src dsq :input people.json :var name='Alice'
SELECT * FROM {} WHERE name = '$name'
#+end_src
#+RESULTS:
| id | name |
|----+-------|
| 1 | Alice |
Passing in a literal number value.
#+begin_src dsq :input people.json :var id=2
SELECT * FROM {} WHERE id = $id
#+end_src
#+RESULTS:
| id | name |
|----+------|
| 2 | Bob |
Passing in the result of a source block that produces a literal value.
#+name: generate-name
#+begin_src ruby
'B' + 'o' + 'b'
#+end_src
#+begin_src dsq :input people.json :var name=generate-name
SELECT * FROM {} WHERE name = '$name'
#+end_src
#+RESULTS:
| id | name |
|----+------|
| 2 | Bob |
Passing in a list.
Note how the values are quoted and joined with commas. In contrast to
passing in a literal value (see above), ob-dsq does assume that a passed
in list is used as a list value in the query.
#+name: players
- Alice
- Bob
#+begin_src dsq :input people.json :var names=players
SELECT * FROM {} WHERE name in ($names)
#+end_src
#+RESULTS:
| id | name |
|----+-------|
| 1 | Alice |
| 2 | Bob |
Passing in a table.
ob-dsq extracts the first column of the table and treats it as a list.
Rules for lists apply here as well.
#+name: scores
| player | score |
|---------+-------|
| Alice | 100 |
| Charles | 20 |
#+begin_src dsq :input people.json :var names=scores
SELECT * FROM {} WHERE name in ($names)
#+end_src
#+RESULTS:
| id | name |
|----+---------|
| 1 | Alice |
| 3 | Charles |