Bug report: json_extract stops prematurely
(1) By Adnan (axel50397) on 2020-09-22 00:16:17 [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:{}}"}}
- Querying:
select json_extract("payload", '$."data"')
returns the complete and correct value ofdata
key. - Querying:
select json_extract("payload", '$."data"."command"')
returns only a portion of thedata.command
path
Thank you for your work
(2) By Richard Hipp (drh) on 2020-09-22 02:09:33 in reply to 1 [link] [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.