SQLite で where json_extract で検索しようとしても Error: SQLITE_ERROR: malformed JSON になるときの対処メモです。
SQLiteで json_extract 便利
最近 SQLiteで json_extract を使うと、そのカラムのデータが JSON のときに、その中からパースしてデータを検索してくれるっていう機能を知って、とても便利で使っています。
このあたりが参考になりました。
なぜか Error: SQLITE_ERROR: malformed JSON が出るようになった
SELECT * from api_log where json_extract(data, '$.status') = 'active'
Node-RED の SQLite ノードで、とある API のレスポンスのログを蓄積して、受け取った JSON のデータの中から status 値をあとで検索して振り返るといった個人的にうれしい仕組みを作っていたところ、700 件くらいデータを貯めたあたりで Error: SQLITE_ERROR: malformed JSON （不正な形式のJSON）が返答されるようになってしまいました。
- 全件でも1件でもうまく検索できない
- そのカラムを対象としない場合はうまく検索できるので他のカラムは正常ぽい
- 一回データを消して蓄積するとログ蓄積再開後 100 件程度だとちゃんと検索できる
- またログを蓄積していると 700 件じゃなくて 300 件程度だったり 1000 件程度だった起きる時期はバラバラ
原因は API のエラーレスポンスをただの文字列で入れてたから
はい。JSON が入っているはずのカラムにその API のエラーレスポンスをただの文字列で入れてたことが原因でした。
そう。この json_extract は、パースする文字列が JSON でないと不正な形式のJSONですよってエラーを返すんです。検索したいカラムで1つでも入っていると検索時に引っかかってエラーになります。
私自身も、ためしに別のテーブルつくって、わざわざ。JSONを {"value1":"ABCDEF"}みたいなデータを {"value1":"ABCDEF} みたいにダブルクォーテーションの囲みを壊してみたら同様のエラーが再現できました。
対処法1：where json_valid が 0 になることで検索し、既存の不正な形式のJSONを探して修正
json_valid を使うとそのカラムのデータが1つ1つ正しいか調査できます。0 になることで検索し、既存の不正な形式のJSONを探すことができます。
SELECT * from api_log where json_valid(data) = 0
これで、一回ながめてから、一気に {"type":"error"} みたいなデータを仮で入れてしまえば、Error: SQLITE_ERROR: malformed JSON 自体は無くなって、また json_extract 検索を使うことができます。
対処法2：そもそも SQLite データを記録するときに、JSONにならない文字列を何らかの形で JSON 修正してから入れる
対処法1 はその場しのぎにはいいんですが、またデータが貯まりだすと API のエラーレスポンスをつかんで記録してしまうと、同じことになります。
なので、テーブルにデータを入れる前に JSON.parse の try catch などを回して JSON の不正を判定するとか、APIのエラーレスポス自体をうまく判定して JSON 形式でデータを入れる処理を入れました。
現在では、不正な JSON 形式はテーブルに入らず、APIエラーレスポンス自体も検索可能でうまく動くようになっています！
そもそも、まず、いろいろ粗くログを貯めてみたかったなーくらいで、来るデータのバリエーションを想像しきれないことに足元をすくわれたところが大きいですが、原因が分かって対処法も見つけられてよかったです！