JSON型、JSONB型、の JSON の null の扱い
まずは JSON演算子の基本から、
"b" 配列の１番目
SELECT '{"a":{"b":[{"c":1},{"c":2},{"c":null}]}}'::jsonb->'a'->'b'->1->>'c'
結果は 文字型で
2
"b" 配列の２番目
SELECT '{"a":{"b":[{"c":1},{"c":2},{"c":null}]}}'::jsonb->'a'->'b'->2->>'c'
結果は 文字型で
null
だから、COALESCE関数を使うと
"b" 配列の１番目は、
SELECT COALESCE('{"a":{"b":[{"c":1},{"c":2},{"c":null}]}}'::jsonb->'a'->'b'->1->>'c', 'This is null')
結果は 文字型で
2
"b" 配列の２番目
SELECT COALESCE('{"a":{"b":[{"c":1},{"c":2},{"c":null}]}}'::jsonb->'a'->'b'->2->>'c', 'This is null')
結果は 文字型で
This is null
JSONの空文字は、
SELECT '{"a":{"b":[{"c":1},{"c":2},{"c":""}]}}'::jsonb->'a'->'b'->2->>'c'
結果は 空文字列
JSON空文字列とJSON null を同等に判定する。
"b" 配列の１番目は、
SELECT COALESCE(NULLIF('{"a":{"b":[{"c":1},{"c":2},{"c":""}]}}'::jsonb->'a'->'b'->1->>'c', '') ,'This is null')
結果は 文字型で
2
"b" 配列の２番目は、
SELECT COALESCE(NULLIF('{"a":{"b":[{"c":1},{"c":2},{"c":""}]}}'::jsonb->'a'->'b'->2->>'c', '') ,'This is null')
も、
SELECT COALESCE(NULLIF('{"a":{"b":[{"c":1},{"c":2},{"c":null}]}}'::jsonb->'a'->'b'->2->>'c', '') ,'This is null')
も、結果は 文字型で
This is null
つまり、
COALESCE( NULLIF( 対象カラム、''), 'This is null' )
で、
NULLIF関数が, NULLIF(value1, value2) の value1 とvalue2 が同じなら NULL を返し
異なれば、value1 の値を返す。ということを利用して、COALESCE 関数を次に実行するのである。
もっとヒステリックに余計な空白文字を除去するなら、
COALESCE( NULLIF( TRIM( 対象カラム )、''), 'This is null' )
であり
SELECT COALESCE(NULLIF(TRIM('{"a":{"b":[" ",""," c "]}}'::jsonb->'a'->'b'->>0), ''),'This is null
の結果は
This is null
SELECT COALESCE(NULLIF(TRIM('{"a":{"b":[" ",""," c "]}}'::jsonb->'a'->'b'->>2), ''),'This is null')
の結果は
c
である
