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
