SQL command not equal comparison within json content

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|

SQL command not equal comparison within json content

tj5527
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: SQL command not equal comparison within json content

Dan Kennedy-4
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
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: SQL command not equal comparison within json content

R Smith-2
In reply to this post by tj5527

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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users