Supporting json column type in database

[thachnb85]

Hi @simolus3,
Sqlite has a json extension to store data in json column, https://www.sqlite.org/json1.html
So for following table:

create table a (id int primary key, data json)

id can be represented as IntColumn, how about data?
Thank you,

[thachnb85]

I tried another way, by creating tables.moor file, and run the generator
I added the data field which has json type

-- this is the tables.moor file
CREATE TABLE todos (
    id INT NOT NULL PRIMARY KEY AUTOINCREMENT,
    title TEXT,
    body TEXT,
    data JSON,
    category INT REFERENCES categories (id)
);

when I run flutter packages pub run build_runner build
I saw some errors:

[INFO] 2.6s elapsed, 1/2 actions completed.
[WARNING] moor_generator:moor_generator on lib/services/data_manager/database/database.dart:
There were some errors while running moor_generator on package:test/services/data_manager/database/database.dart:
[WARNING] moor_generator:moor_generator on lib/services/data_manager/database/database.dart:
line 6, column 14: Expected a JSON KEY constraint
  ╷
6 │     data JSON,
  │              ^
  ╵

[thachnb85]

Thanks @Mike278 Mike

I checked and printed every single row, the string for json is always correct, using double quotes:

{"index":"1"}

Starts with curly bracket, key and value are correct, and ending with curly bracket.
Using the sqlite command line, everything is correct, query is fine.
I just want to test the moor plugin if it really works with custom select query like above.

[thachnb85]

https://github.com/thachnb85/moor_shared
I have some example data and query, forked from moor_shared and added json test code so you can reproduce the issue above. Did moor modify the customQuery? @simolus3

[Mike278]

Ah I see - thanks for the repro!

If you change jsonData: Value('"{"index":"$i"}"') to what you had earlier (jsonData: Value('{"index":"$i"}')), does it work?

[thachnb85]

I tried both, it doesn't work.

[thachnb85]

@Mike278 I found the issue!

I got the sql.js code from https://github.com/sql-js/sql.js
Then modify the Makefile to support JSON1, but after running the build command as they guided: npm run rebuild
When I use this output, JSON1 is supported.

sql-wasm-debug.js
sql-wasm-debug.wasm

When I use the release one

sql-wasm.js
sql-wasm.wasm

these files don't support JSON1

So nothing wrong with moor_shared project above.
The correct json text format should not begin with double quotes jsonData: Value('"{"index":"$i"}"')
Correct format of json is:jsonData: Value('{"index":"$i"}')