4 ms·
I love this example from the docs: select line -> '$.id' as id, line -> '$.name' as name from lines_read("my-file.ndjson"); This is using
by simonw 4y ago
I love this example from the docs:
select
line -> '$.id' as id,
line -> '$.name' as name
from lines_read("my-file.ndjson");
This is using the new -> JSON operator which was added to SQLite a few months ago.
The lines_read() thing there is a table-valued function, which means it returns a virtual table that you can query. This is a streaming operation which means it's safe to run it against a 100GB+ file without worrying about it sucking all of the data into memory at once.
Where this gets really useful is when you combine it with a create table statement:
create table names as select
line -> '$.id' as id,
line -> '$.name' as name
from lines_read("my-file.ndjson");
This should efficiently create a table with that exact subset of the data pulled from the newline-delimited JSON file.
- nextaccountic 4y ago> This is using the new -> JSON operator which was added to SQLite a few months ago. Is this available in Postgresql? Or what would be the equivalent?
- dragonwriter 4y agoPostgres has similar JSON access operators, but slightly different syntax: https://www.postgresql.org/docs/current/functions-json.html https://www.postgresql.org/docs/current/functions-json.html
- simonw 4y agoI believe the SQLite operators were directly inspired by PostgreSQL and MySQL. There's a detailed comparison here: https://sqlite.org/src/doc/json-enhancements/doc/json-enhancements.md https://sqlite.org/src/doc/json-enhancements/doc/json-enhanc...
- SPBS 4y agoI believe this https://sqlite.org/src/doc/json-in-core/doc/json-enhancements.md https://sqlite.org/src/doc/json-in-core/doc/json-enhancement... is more up to date, as json_nextract is no longer in the core functions.