JSON1? #259

brodycj · 2018-12-21T15:13:11Z

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 · 2018-12-22T11:12:40Z

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 · 2019-01-15T19:12:55Z

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 · 2019-03-08T09:10:56Z

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

hoogw · 2019-07-09T18:32:36Z

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 · 2019-07-09T18:33:31Z

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

brodycj · 2019-07-09T18:43:06Z

[…] 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 · 2019-07-09T21:02:57Z

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 · 2019-07-09T21:07:30Z

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 · 2019-07-09T22:44:42Z

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 · 2019-12-15T16:04:42Z

@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 · 2019-12-15T17:37:21Z

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 · 2019-12-15T18:23:47Z

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

lovasoa · 2019-12-15T18:30:38Z

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

jsmorph · 2019-12-15T18:34:06Z

It's a demo.

KevinKelchen · 2020-04-02T21:51:31Z

Hi @brodybits, 👋

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

It looks like WASM was disabled. Does that mean you had to use asm.js for JSON1 support?
Was it a requirement to update SQLite?
Were the other features like FTS4, FTS5, and R-Tree required for JSON1 support?
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 · 2020-04-02T22:16:14Z

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.

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 · 2020-04-03T01:06:42Z

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

KevinKelchen · 2020-04-03T15:30:30Z

@brodybits,

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

KevinKelchen · 2021-03-15T15:07:27Z

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 · 2024-03-15T13:18:55Z

👋🏼 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 · 2024-03-17T19:42:45Z

👋🏼 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', '[email protected]'
);
-- '{"name":"Alice","age":25,"email":"[email protected]"}'

SELECT JSON_ARRAY(
    'Alice',
    25,
    '[email protected]'
);
-- '["Alice",25,"[email protected]"]'

-- 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

lovasoa added the enhancement label Dec 22, 2018

kaizhu256 mentioned this issue Mar 3, 2021

enable json1 extension #440

Merged

lovasoa closed this as completed in #440 Mar 15, 2021

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

JSON1? #259

JSON1? #259

brodycj commented Dec 21, 2018

lovasoa commented Dec 22, 2018

intijk commented Jan 15, 2019

Yavari commented Mar 8, 2019

hoogw commented Jul 9, 2019

hoogw commented Jul 9, 2019

brodycj commented Jul 9, 2019

hoogw commented Jul 9, 2019 •

edited by lovasoa

Loading

hoogw commented Jul 9, 2019 •

edited by lovasoa

Loading

lovasoa commented Jul 9, 2019 •

edited

Loading

jsmorph commented Dec 15, 2019

lovasoa commented Dec 15, 2019

jsmorph commented Dec 15, 2019 •

edited

Loading

lovasoa commented Dec 15, 2019

jsmorph commented Dec 15, 2019

KevinKelchen commented Apr 2, 2020

brodycj commented Apr 2, 2020

kaizhu256 commented Apr 3, 2020

KevinKelchen commented Apr 3, 2020

KevinKelchen commented Mar 15, 2021

james-bowers commented Mar 15, 2024 •

edited

Loading

kaizhu256 commented Mar 17, 2024

JSON1? #259

JSON1? #259

Comments

brodycj commented Dec 21, 2018

lovasoa commented Dec 22, 2018

intijk commented Jan 15, 2019

Yavari commented Mar 8, 2019

hoogw commented Jul 9, 2019

hoogw commented Jul 9, 2019

brodycj commented Jul 9, 2019

hoogw commented Jul 9, 2019 • edited by lovasoa Loading

hoogw commented Jul 9, 2019 • edited by lovasoa Loading

lovasoa commented Jul 9, 2019 • edited Loading

jsmorph commented Dec 15, 2019

lovasoa commented Dec 15, 2019

jsmorph commented Dec 15, 2019 • edited Loading

lovasoa commented Dec 15, 2019

jsmorph commented Dec 15, 2019

KevinKelchen commented Apr 2, 2020

brodycj commented Apr 2, 2020

kaizhu256 commented Apr 3, 2020

KevinKelchen commented Apr 3, 2020

KevinKelchen commented Mar 15, 2021

james-bowers commented Mar 15, 2024 • edited Loading

kaizhu256 commented Mar 17, 2024

hoogw commented Jul 9, 2019 •

edited by lovasoa

Loading

hoogw commented Jul 9, 2019 •

edited by lovasoa

Loading

lovasoa commented Jul 9, 2019 •

edited

Loading

jsmorph commented Dec 15, 2019 •

edited

Loading

james-bowers commented Mar 15, 2024 •

edited

Loading