JSON1?

[brodycj]

A major client needs JSON1 support in SQL.js. Can we add it to SQL.js or would we have to maintain a fork?

[lovasoa]

Hi ! We currently do not support sqlite extensions. However, it should be easy to compile your own sql.js with json1 support by editing the Makefile...

[intijk]

Hi @lovasoa

I have similar needs of json. My first step is to make the repo without any changes, but i meet this:

$ make
# Generate llvm bitcode
emcc -O2 -DSQLITE_OMIT_LOAD_EXTENSION -DSQLITE_DISABLE_LFS -DLONGDOUBLE_TYPE=double -DSQLITE_THREADSAFE=0 -DSQLITE_ENABLE_FTS3 -DSQLITE_ENABLE_FTS3_PARENTHESIS c/sqlite3.c -o c/sqlite3.bc
emcc --memory-init-file 0 -O3 -s INLINING_LIMIT=50 -s RESERVED_FUNCTION_POINTERS=64 -s EXPORTED_FUNCTIONS=@exported_functions -s EXTRA_EXPORTED_RUNTIME_METHODS=@exported_runtime_methods c/extension-functions.bc c/sqlite3.bc --post-js js/api.js -o js/sql-optimized-raw.js ;\

WARNING  root: Assigning a non-existent settings attribute "EXTRA_EXPORTED_RUNTIME_METHODS"
WARNING  root:  - did you mean one of ORIGINAL_EXPORTED_FUNCTIONS, EXPORTED_FUNCTIONS?
WARNING  root:  - perhaps a typo in emcc's  -s X=Y  notation?
WARNING  root:  - (see src/settings.js for valid values)
Traceback (most recent call last):
  File "/usr/bin/emcc", line 1547, in <module>
    extra_files_to_link = system_libs.calculate([f for _, f in sorted(temp_files)], in_temp, stdout, stderr, forced=forced_stdlibs)
  File "/usr/share/emscripten/tools/system_libs.py", line 502, in calculate
    add_back_deps(symbols)
  File "/usr/share/emscripten/tools/system_libs.py", line 483, in add_back_deps
    shared.Settings.EXPORTED_FUNCTIONS.append('_' + dep)
AttributeError: 'str' object has no attribute 'append'
Makefile:24: recipe for target 'js/sql-optimized-raw.js' failed
make: *** [js/sql-optimized-raw.js] Error 1

Did I do some thing wrong?

[Yavari]

I am getting the same error when I try to build. Are there any documentation on what the build dependencies are?

[hoogw]

I need json1, json1 is a must have feature for sql.js. Why? Because when use sql.js in browser, get result, the standard format is json, instead of array of value and array of column.

[hoogw]

Please add json1 extension. It is a MUST have feature.

[brodycj]

[…] json1 is a must have feature

I suggest you add an upvote (thumbs up) to the description. I think sufficient upvotes should help motivate the maintainers to reconsider the decision.

The ugly workaround is to maintain a special branch, with dist built with JSON1 enabled, as I do in my outdated fork.

[hoogw]

This is my work around,

_result_json =  json1_extension(your_raw_records);

function json1_extension(_records){
    var _json = [];

    var _columns = _records[0].columns
    var _values = _records[0].values

    for (var i = 0; i < _values.length; i++) {
        //console.log(_values[i]);
        var _row_json = {};

        var _row = _values[i];
        for (var k = 0; k < _row.length; k++) {
            _row_json[_columns[k]] = _row[k]
        }

        //console.log('_row_json...',_row_json);
        _json.push(_row_json)
    }
    return _json
}

[hoogw]

with json1, my sql will be:

var _sql = "SELECT json_group_array( json_object('id', id, 'name', name)    ) AS json_result FROM (SELECT * FROM ipfs ORDER BY id); ";

but, since it not support, json1, error out, I made above function manually convert record array to json.

[lovasoa]

Hello @hoogw, If I understand your use case correctly, all you want is getting your query results as JavaScript objects instead of arrays. This is already possible using stmt.getAsObject. You can read more about it in the README. Also, this is an free software project maintained by volunteers. There is no MUST have features.

[jsmorph]

@intijk I got the error you reported above. If I install EMSDK per these directions, everything works.

JSON1 is included with the SQLite "amalgations", so just adding -DSQLITE_ENABLE_JSON1 to CFLAGS in Makefile works!

var x = {"two":2,"three":3},
    js = JSON.stringify(x),
    src = "SELECT JSON_EXTRACT('" + js + "', '$.three')";
console.log("JSON_EXTRACT", db.exec(src));

[lovasoa]

var x = {"two":2,"three":3},
    js = JSON.stringify(x),
    src = "SELECT JSON_EXTRACT('" + js + "', '$.three')";
console.log("JSON_EXTRACT", db.exec(src));

I would avoid doing something like that. This is vulnerable to SQL injections.

[jsmorph]

@lovasoa Running in the user's browser, so the user can self-inject as desired!

[lovasoa]

In any case, this is a bug. The code above just doesn't work if the json contains the character '

[jsmorph]

It's a demo.

[KevinKelchen]

Hi @brodybits, 👋

I was checking out your fork trying to see what steps are needed to create my own fork.

1. It looks like WASM was disabled. Does that mean you had to use asm.js for JSON1 support?
2. Was it a requirement to update SQLite?
3. Were the other features like FTS4, FTS5, and R-Tree required for JSON1 support?
4. It looks like a special build command was used as well:

   LLVM=/usr/local/homebrew/Cellar/emscripten/1.38.21/libexec/llvm/bin make

Is there a list of steps for someone like me who doesn't know much about this project to add JSON1 support in a fork?

I, too, wish that sql.js supported JSON1 out of the box. 🙂

Thanks!

Kevin

[brodycj]

I was checking out your fork trying to see what steps are needed to create my own fork.

I recommend you check out some of my more recent branches. You should be able to see my recent work to support JSON1 along with some other features including some non-standard features here, should be able to build both asm.js and WASM now: https://github.com/brodybits/sql.js/commits/eu-ext-custom-build

You may also be interested in my custom functions proposal in PR #320.

1. It looks like WASM was disabled. Does that mean you had to use asm.js for JSON1 support?

I had trouble using WASM in the older fork, should be OK now.

2. Was it a requirement to update SQLite?

In terms of customer requirement: yes but not a super-hard requirement.

In terms of build requirement: it should be at least theoretically possible to build JSON1 with older versions of SQLite but I cannot see much benefit here.

3. Were the other features like FTS4, FTS5, and R-Tree required for JSON1 support?

I think no. I generally build with all of these features included for the benefit of my users.

4. It looks like a special build command was used as well:

I think that was not needed to build my newer branches. I had the best luck installing emcc tool from their installer on GitHub, I think it was https://github.com/emscripten-core/emsdk but not 100% sure now.

Is there a list of steps for someone like me who doesn't know much about this project to add JSON1 support in a fork?

I would recommend that you first install the emcc or emsdk tooling and try building from the master branch of this project. Then you should be able to edit the flags to enable JSON1 and build. You are also welcome to look

Good luck!

[kaizhu256]

since sql.js targets javascript, i tend to agree json1 should be a builtin feature.

[KevinKelchen]

@brodybits,

In case you didn't see my reaction on your comment: THANK YOU very much! And especially for responding so quickly! ❤️

[KevinKelchen]

This is awesome! 😀

I had been trying out @dasha.ai/sql.js as a pre-built package that adds JSON1 support but now it's built into the original library!

[james-bowers]

👋🏼 Hi! I'm late to the party here, but what data type should JSON be inserted as into a table? Is it a text data type? I can't find it in the docs so I'm just experimenting now trying various things 👀

UPDATE 1;

On this blog post I've seen there's a json data type; https://www.beekeeperstudio.io/blog/sqlite-json - but I get an error;

Error: no such function: json_encode

perhaps this is the old way using the extension?

UPDATE 2;

I've made progress using the json data type, and using a json function instead of the json_encode function

[kaizhu256]

👋🏼 Hi! I'm late to the party here, but what data type should JSON be inserted as into a table? Is it a text data type? I can't find it in the docs so I'm just experimenting now trying various things 👀

• there's no json_encode function in vanilla SQLite
  • instead there's json_object and json_array:

    
    SELECT JSON_OBJECT(
    'name', 'Alice',
    'age', 25,
    'email', 'alice@example.com'
    );
    -- '{"name":"Alice","age":25,"email":"alice@example.com"}'

SELECT JSON_ARRAY( 'Alice', 25, 'alice@example.com' ); -- '["Alice",25,"alice@example.com"]'

-- nested json example SELECT JSON_ARRAY( JSON_ARRAY(1, 2), JSON_OBJECT('name', 'Alice') ) -- '[[1,2],{"name":"Alice"}]'

- official JSON1 usage examples can be found @ https://www.sqlite.org/json1.html
- yes, like `date`, and `datetime`, `json` is typically stored as a text datatype in SQLITE
    - element-extraction, however, is dynamic and will auto-select correct datatype (using `->>` operator)
    - see https://www.sqlite.org/json1.html#the_and_operators