SQLite Forum

Bug report: json_extract stops prematurely

Bug report: json_extract stops prematurely

(1) By Adnan (axel50397) on 2020-09-22 00:16:17 [link] [source]

Test data in a JSON column (called payload):

{"data":{"command":"O:21:\"App\\Jobs\\ParseInbound\":11:{s:10:\"\u0000*\u0000inbound\";s:12:\"Hello World\n\";s:7:\"\u0000*\u0000team\";O:45:\"Illuminate\\Contracts\\Database\\ModelIdentifier\":4:{s:5:\"class\";s:15:\"App\\Models\\Team\";s:2:\"id\";i:1;s:9:\"relations\";a:0:{}s:10:\"connection\";s:6:\"sqlite\";}s:3:\"job\";N;s:10:\"connection\";N;s:5:\"queue\";N;s:15:\"chainConnection\";N;s:10:\"chainQueue\";N;s:19:\"chainCatchCallbacks\";N;s:5:\"delay\";N;s:10:\"middleware\";a:0:{}s:7:\"chained\";a:0:{}}"}}
  1. Querying: select json_extract("payload", '$."data"') returns the complete and correct value of data key.
  2. Querying: select json_extract("payload", '$."data"."command"') returns only a portion of the data.command path

Thank you for your work

(2) By Richard Hipp (drh) on 2020-09-22 02:09:33 in reply to 1 [source]

The value of $.data.command is a string. But SQLite does not have the ability to deal with strings containing embedded \u0000 characters. SQLite stops at the first zero-character.

If you want to deal with zero bytes, you need to use a BLOB. Unfortunately JSON functions in SQLite do not currently support a method of extracting a JSON string as a BLOB.

(3) By Adnan (axel50397) on 2020-09-22 12:32:24 in reply to 2 [link] [source]

Hi Richard,

Thanks for your reply. I was just reporting the "bug" (which apparently is already known). I'm currently only looking for a string inside, so I'm not extracting and using LIKE instead anyway.