3 ms·
I would love for sqlite to add import of JSON. They have great CSV import and export. If we could easily import, say, JSON arrays to a single column table, it w
by fegu 5y ago
I would love for sqlite to add import of JSON. They have great CSV import and export. If we could easily import, say, JSON arrays to a single column table, it would make JSON conversion to/from CSV easier. Especially since jq is slow and unmaintained.
- porker 5y agoMe too.
- xtreak29 5y agoThere are couple of threads related to json import but a default mode will be helpful as there is already .mode json for output and import support will be great. https://sqlite.org/forum/forumpost/567caa57234b98e46b885bcf86a22526bffaf603f777e25e22b720de0e9a89c9 https://sqlite.org/forum/forumpost/567caa57234b98e46b885bcf8... https://sqlite.org/forum/forumpost/f4b44e09cea20851014420a8b91cea8254c14951635307d964ab6c9fa283c982 https://sqlite.org/forum/forumpost/f4b44e09cea20851014420a8b... sqlite-utils is also a helpful project for various utilities : https://sqlite.org/forum/forumpost/ff5b6198eb6e794d2f774ce06b2ae7aab3163c52ad5ba7f093494f008a8236d9 https://sqlite.org/forum/forumpost/ff5b6198eb6e794d2f774ce06... I guess you are trying to do below from your comment. $ echo '["Kate", "John", "Jim"]' > names.json $ ./sqlite3 SQLite version 3.38.0 2022-02-22 18:58:40 Enter ".help" for usage hints. Connected to a transient in-memory database. Use ".open FILENAME" to reopen on a persistent database. sqlite> create table users(id integer primary key, name text); sqlite> insert into users(name) select value from json_each(readfile('names.json')); sqlite> select * from users; 1|Kate 2|John 3|Jim
- e12e 5y agoIs this: select value from json_each(readfile('names.json')) Built in to (standard) sqlite? It's a little unclear from your comment?
- xtreak29 5y agoIt seems readfile is from an extension but present in cli. json_each is present in standard sqlite core from 3.38.0 as json1 extension is also now part of core. json_each : https://www.sqlite.org/json1.html#jeach https://www.sqlite.org/json1.html#jeach readfile : https://sqlite.org/cli.html#file_i_o_functions https://sqlite.org/cli.html#file_i_o_functions > Note that the readfile(X) and writefile(X,Y) functions are extension functions and are not built into the core SQLite library. These routines are available as a loadable extension in the ext/misc/fileio.c source file in the SQLite source code repositories.
- rovr138 5y agojson1 being part of core is pretty big for me. I won’t have to build one anymore.
- globular-toast 5y agoSqlite-utils has this: https://github.com/simonw/sqlite-utils https://github.com/simonw/sqlite-utils
- adamgordonbell 5y agoSqlite utils is amazing. It just removes a lot of frictions from one off data tasks.
- hnlmorg 5y agoShameless self promotion but this is already possible using my shell https://github.com/lmorg/murex https://github.com/lmorg/murex It works with YAML, TOML, JSON, jsonlines, CSV, and regular shell command output. You can import from any data format and convert to any other data format and even in line SQL relational look ups too. Since SQL inlining literally just imports the data into an in memory sqlite3 database it means you can do your JSON import into sqlite3 using this shell. And in fact I did literally just this last month when using a cloud service restful API which returned two different JSON docs that needed to restructured into two different tables and then a relational query run between them.
- deleted 5y ago[deleted]