Is it possible to wildcard read JSON files?

[gordonjb]

Hi all, I'm looking to put a query I've tested in DuckDB into a Framework table, is it possible to use FROM read_json('py/out/*.json')? When I try to put this straight into an ```sql block, I get the following errors:

RuntimeError: IO Error: No files found that match the pattern "py/out/*.json"
RuntimeError: IO Error: No files found that match the pattern "py/out/*.json"
RuntimeError: IO Error: No files found that match the pattern "py/out/*.json"
Error: IO Error: No files found that match the pattern "py/out/*.json"

I've tried to use FROM read_json(${FileAttachment("./py/out/*.json").href}) as I think the docs suggest that you have to use FileAttachments, but I still see this error even if I specify a specific sole JSON file:

RuntimeError: Invalid Error: [object WebAssembly.Exception]
RuntimeError: Invalid Error: [object WebAssembly.Exception]
RuntimeError: Invalid Error: [object WebAssembly.Exception]
Error: Invalid Error: [object WebAssembly.Exception]

I also tried to use the SQL opening matter, but that doesn't seem to support wildcards either, and ideally I'd like to specify the schema of the JSON (hence the use of read_json)

[Fil]

The short answer is no, everything you build in framework must be known statically in advance. (The long answer of course might be more complicated… for example, you can totally use that pattern when querying remote files.)

[gordonjb]

Ah I see, that does make sense now you've pointed it out. I probably would have ended up moving the SQL statements to a duckdb dataloader in the long run anyway, I assume I wouldn't have the same problem there? (Just obviously the dataloader won't automatically update if the files in py/out change but that's fine)

[gordonjb]

I've spotted the duckdb file support added in 1.4 (https://github.com/observablehq/framework/releases/tag/v1.4.0), which suggests you can create a duckdb file from a dataloader. I wrote a tiny shell dataloader, shows.db.sh:

duckdb showstmp.db << EOF
CREATE TABLE shows AS 
SELECT * 
FROM read_json('py/out/*.json');
EOF

cat showstmp.db >&1  # Write output to stdout
rm showstmp.db

I've confirmed that running the script loads the data and prints it to the console, however observable never seems to run it? If I put the following in the front matter:

---
theme: dashboard
sql:
  shows: shows.db
---

and start the server:

npm run dev --debug

> dev
> observable preview

Observable Framework v1.13.0
↳ http://127.0.0.1:3000/

GET /
GET /_observablehq/theme-air,near-midnight,alt,wide.css
GET /_observablehq/stdlib/inputs.css
GET /_observablehq/client.js
GET /_observablehq/runtime.js
GET /_observablehq/stdlib.js
GET /_observablehq/stdlib/inputs.js
GET /_npm/@duckdb/[email protected]/_esm.js
GET /_observablehq/stdlib/duckdb.js
GET /_npm/[email protected]/_esm.js
GET /_npm/[email protected]/_esm.js
GET /_npm/[email protected]/_esm.js
GET /_npm/[email protected]/_esm.js
GET /_npm/[email protected]/_esm.js
GET /_npm/@duckdb/[email protected]/dist/duckdb-browser-eh.worker.js
GET /_npm/@duckdb/[email protected]/dist/duckdb-eh.wasm
GET /_file/shows.db?sha=e3b0c44298fc1c149afbf4c8996fb92427ae41e4649b934ca495991b7852b855
HEAD /_file/shows.db?sha=e3b0c44298fc1c149afbf4c8996fb92427ae41e4649b934ca495991b7852b855
GET /_file/shows.db?sha=e3b0c44298fc1c149afbf4c8996fb92427ae41e4649b934ca495991b7852b855
GET /_file/shows.db?sha=e3b0c44298fc1c149afbf4c8996fb92427ae41e4649b934ca495991b7852b855
HEAD /_file/shows.db?sha=e3b0c44298fc1c149afbf4c8996fb92427ae41e4649b934ca495991b7852b855
GET /_file/shows.db?sha=e3b0c44298fc1c149afbf4c8996fb92427ae41e4649b934ca495991b7852b855
socket open /_observablehq
↑ {
  type: 'hello',
  path: '/',
  hash: '9fbc3c2174b2921aaf2f9e03fc539136663b9fcba500e9b391e06b4c58a74e85'
}
socket open /_observablehq
↓ { type: 'welcome' }
↑ {
  type: 'hello',
  path: '/',
  hash: '9fbc3c2174b2921aaf2f9e03fc539136663b9fcba500e9b391e06b4c58a74e85'
}
socket open /_observablehq
↓ { type: 'welcome' }
↑ {
  type: 'hello',
  path: '/',
  hash: '9fbc3c2174b2921aaf2f9e03fc539136663b9fcba500e9b391e06b4c58a74e85'
}
↓ { type: 'welcome' }

I just get an error that the file doesn't exist, and indeed no DB files are generated in the cache:

RuntimeError: IO Error: Cannot open database "shows.db" in read-only mode: database does not exist
RuntimeError: IO Error: Cannot open database "shows.db" in read-only mode: database does not exist
RuntimeError: IO Error: Cannot open database "shows.db" in read-only mode: database does not exist
Error: IO Error: Cannot open database "shows.db" in read-only mode: database does not exist

[Fil]

Not sure what's happening. I've tried to do something similar and it worked for me. Here's my set-up:

❯ cat penguins.db.sh

duckdb tmp-penguins.db <<EOF
CREATE TABLE penguins AS FROM 'https://gist.githubusercontent.com/brojonat/8daddc85077fa4b384f549ef6283fbe7/raw/7a3ef2dba96f33bde91014aac9e4c5f515511789/penguins.json';
EOF

cat tmp-penguins.db
rm tmp-penguins.db

❯ cat penguins.md

---
sql:
  db: ./penguins.db
---

# Hello, penguins

```sql echo
SELECT HISTOGRAM(island)
  FROM db.penguins;
```

[gordonjb]

Yeah strangely, your set up also isn't working for me, but at least that means it's probably something wrong on my end rather than my approach was wrong. I'll have an investigate.

EDIT: No change with a freshly created Framework project.

[gordonjb]

Issue solved: file paths were the problem. I was putting my dataloaders in /data, so my front matter needed to be

---
sql:
  db: data/penguins.db
---

I confirmed it with my original dataloader and was able to load my duckdb table from my JSON files.