JSON1?

[brody4hire]

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.

[brody4hire]

[…] 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.