1

I have some data in a table in sqlite database. The table has only one column with data as JSON records. 

sqlite> .schema feeds3
CREATE TABLE feeds3 (data text NOT NULL);

When I am querying from the table using json_extract I can see the data getting fetched from the JSON path. But when I am trying to use the same json_extract in order by clause it gives me error saying malformed JSON.

sqlite> select json_extract(data,'$.ps') from feeds3 limit 1;
AA1227
sqlite> select * from feeds3 order by json_extract(data,'$.ps') limit 1;
Error: malformed JSON

The json1 module I installed is by downloading the json1.c file from the below link : https://sqlite.org/cgi/src/raw/ext/misc/json1.c?name=9799e4252b305edcbe659329eec3ca80ed85f968

and followed below steps

 gcc -g -fPIC -dynamiclib json1.c -o json1
 sqlite> .load json1

Sqlite version:sqlite-snapshot-201904101324

oz123
  • 27,559
  • 27
  • 125
  • 187
dks551
  • 1,113
  • 1
  • 15
  • 39
  • When building from a source tarball, just pass `--enable-json1` to `configure` to get it built in, btw. No need to compile it separately in that case. – Shawn Apr 17 '19 at 13:54
  • 1
    And show some sample data for your table? I'm guessing at least one row has invalid json. – Shawn Apr 17 '19 at 13:55
  • Yeah, the obvious answer is that you have bad data somewhere. If you want anything more than that you'll have to provide some examples of data that's failing. – glibdud Apr 17 '19 at 14:08
  • but if it is able to extract it from the bad data, then why can't we use it in order by. – dks551 Apr 17 '19 at 15:11
  • A `LIMIT` by itself just picks arbitrary rows and you got lucky. Sorting the results, on the other hand, usually requires looking at every row. – Shawn Apr 17 '19 at 15:21

0 Answers0