Re: [sqlite] SQL command not equal comparison within json content
On 2018/02/28 3:34 PM, tj5527 wrote:
I create a table with the command `CREATE TABLE test1 (key text primary key,
obj json);` with two records inserted
# select * from test1;
key1|{"a":1,"b":2,"c":{"x":99},"status":"done"} # record 1
key2|{ "key": key2 } # record 2
Now I want to retrieve the record that is not marked with status "done" (so basically it is
expected to return the second record i.e. record 2 with key2). The command I use is `select * from test1,
json_tree(test1.obj) where json_tree.value <> "done";` But it returns
key1|{"a":1,"b":2,"c":{"x":99},"status":"done"}|a|1|integer|1|2|0|$.a|$
key1|{"a":1,"b":2,"c":{"x":99},"status":"done"}|b|2|integer|2|4|0|$.b|$
key1|{"a":1,"b":2,"c":{"x":99},"status":"done"}|c|{"x":99}|object||6|0|$.c|$
key1|{"a":1,"b":2,"c":{"x":99},"status":"done"}|x|99|integer|99|8|6|$.c.x|$.c
Error: malformed JSON
It's just like the error says: The JSON is malformed, it has nothing to
do with SQLite. You can easily check the correctness of any JSON on one
of the JSON validation sites, like this:
https://jsonlint.com/
If you go to that site and paste your json string from key 2:
{ "key": key2 }
in there, then hit "Validate", it will tell you why it is wrong.
SQLite version I use is 3.11.0 2016-02-15 17:29:24
That version is archaic, we are already past 3.20 - and there were some
JSON enhancements in the time it took, which might benefit you. Are you
able to use a more recent version?
Cheers,
Ryan
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQL command not equal comparison within json content
On 02/28/2018 08:34 PM, tj5527 wrote:
I create a table with the command `CREATE TABLE test1 (key text primary key,
obj json);` with two records inserted
# select * from test1;
key1|{"a":1,"b":2,"c":{"x":99},"status":"done"} # record 1
key2|{ "key": key2 } # record 2
The second record is not valid json because "key2" is not quoted.
Causing the error.
Dan.
Now I want to retrieve the record that is not marked with status "done" (so basically it is
expected to return the second record i.e. record 2 with key2). The command I use is `select * from test1,
json_tree(test1.obj) where json_tree.value <> "done";` But it returns
key1|{"a":1,"b":2,"c":{"x":99},"status":"done"}|a|1|integer|1|2|0|$.a|$
key1|{"a":1,"b":2,"c":{"x":99},"status":"done"}|b|2|integer|2|4|0|$.b|$
key1|{"a":1,"b":2,"c":{"x":99},"status":"done"}|c|{"x":99}|object||6|0|$.c|$
key1|{"a":1,"b":2,"c":{"x":99},"status":"done"}|x|99|integer|99|8|6|$.c.x|$.c
Error: malformed JSON
What is the correct SQL command to achieve such effect?
SQLite version I use is 3.11.0 2016-02-15 17:29:24
Thanks
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] SQL command not equal comparison within json content
I create a table with the command `CREATE TABLE test1 (key text primary key,
obj json);` with two records inserted
# select * from test1;
key1|{"a":1,"b":2,"c":{"x":99},"status":"done"} # record 1
key2|{ "key": key2 } # record 2
Now I want to retrieve the record that is not marked with status "done" (so
basically it is expected to return the second record i.e. record 2 with key2).
The command I use is `select * from test1, json_tree(test1.obj) where
json_tree.value <> "done";` But it returns
key1|{"a":1,"b":2,"c":{"x":99},"status":"done"}|a|1|integer|1|2|0|$.a|$
key1|{"a":1,"b":2,"c":{"x":99},"status":"done"}|b|2|integer|2|4|0|$.b|$
key1|{"a":1,"b":2,"c":{"x":99},"status":"done"}|c|{"x":99}|object||6|0|$.c|$
key1|{"a":1,"b":2,"c":{"x":99},"status":"done"}|x|99|integer|99|8|6|$.c.x|$.c
Error: malformed JSON
What is the correct SQL command to achieve such effect?
SQLite version I use is 3.11.0 2016-02-15 17:29:24
Thanks
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
