PostgreSQLのjsonb型で空値を検索する

業務でpostgresのjsonb型を利用した検索機能を作成するにあたって、引っかかったところをまとめておきます。

想定しているデータ

下記のようなjsonb型の列を持つテーブルに対して検索処理を行うパターンを想定します。
概ね構造は一致していますが、keyがnullやundefined、空の配列が混在しています。

CREATE TABLE book (
    id serial PRIMARY KEY,
    json_data jsonb
);

INSERT INTO book (json_data)
VALUES (
    '{
        "title": "人間失格",
        "author" : "太宰 治",
        "read" : null,
        "option": null
    }'::jsonb
),(
    '{
        "title": "こころ",
        "author" : "夏目 漱石",
        "read" : null,
        "has_duplicate_author" : true,
        "option": {
            "tag" : ["school"]
        }
    }'::jsonb
),(
    '{
        "title": "吾輩は猫である",
        "author" : "夏目 漱石",
        "read" : true,
        "has_duplicate_author" : true,
        "option": {
            "tag" : []
        }
    }'::jsonb
);

類似の型

postgresにはjson型とjsonb型の二つがあります。単純な保存であればjson型を使い、検索操作などを実行する場合はjsonb型を使います。
jsonb型への変換にはjsonでそのまま保存するよりも時間がかかりますが、それほど重い処理ではないためデータ量が大きい場合や速度要件が厳しい場合でなければ後々の拡張性を考え最初からjsonb型にしておく方が概ね良さそうです。

値を取得する

SELECT json_data->'author' FROM book;
SELECT json_data->'option'->'tag' FROM book;

特定のキーの値が一致する検索

SELECT json_data FROM book
WHERE json_data->'author' = '"太宰 治"';
SELECT json_data FROM book
WHERE json_data @@ '$.author == "太宰 治"';
-- 下記は条件を満たす構造を持つチェックするため、optionなどに同じ構造があってもヒットする。
SELECT json_data FROM book
WHERE json_data @> '{"author" : "太宰 治"}';

特定のキーが空かどうかを検索する

通常のSQLであれば列の値が存在するかどうかはIS NOT NULLかデフォルト値との一致比較で検索します。
しかし、jsonb型の場合は構造に自由度がある関係でやや複雑になります。具体的には下記のパターンがありえます

①パスが存在する

SELECT json_data FROM book WHERE json_data @? '$.has_duplicate_author';

②パスが存在し、さらにnullでない

SELECT json_data FROM book 
WHERE json_data @? '$.option.tag'
AND (jsonb_typeof(jsonb_path_query_first(json_data,'$.option.tag')) != 'null');

jsonb_path_queryもありますが、こちらは複数行返却されることがあるためerrorとなります。
またjsonb_path_query_first(json_data,'$.option.tag')をjson_data->'$.option.tag'とすることもできます。

③パスが存在し、さらにnullでなく、空の配列でもない。

SELECT json_data FROM book 
WHERE json_data @? '$.option.tag'
AND (jsonb_typeof(jsonb_path_query_first(json_data,'$.option.tag != null')) != 'null')
AND (
    jsonb_typeof(jsonb_path_query_first(json_data,'$.option.tag')) = 'array'
    AND jsonb_array_length(jsonb_path_query_first(json_data,'$.option.tag')) != 0
    );

参考

The PostgreSQL Global Development Group翻訳日本PostgreSQLユーザ会 データ型
The PostgreSQL Global Development Group翻訳日本PostgreSQLユーザ会 関数と演算子