😎

PostgreSQL で JSON への変換に失敗したら null を返す Function を作成する

2024/03/13に公開

PostgreSQL

SQL

tech

PostgreSQL を使用して、あるテーブル X のカラム A に格納されている TEXT 型の JSON 文字列の値を使用したかったので、 JSON 型に変換して云々する SQL を書いていました^[1]。

ここで、TEXT 型で JSON 文字列が入ってしまう + Nullable である以上、JSON 型への変換が 100% 保証されないので try_cast 的な関数を挟みたいと考えたのですが、PostgreSQL にはそういった関数がないらしいので、実装しました。

まとめ

JSON に変換することができない文字列を cast しようとするとエラーが発生する
BEGIN で変換を試みて、EXCEPTION で例外を拾う

JSON に変換することができない文字列を cast しようとするとエラーが発生する

TEXT 型の文字列を JSON 型に変換する場合、以下のようにキャストすることができます。

SELECT '{"k": "valid_value"}'::json

指定の値を取り出したい場合は、

SELECT '{"k": "valid_value"}'::json->>'k' /* => valid_value */

しかし、変換することができない文字列に関しては、以下のようにエラーが発生してしまいます。

SELECT '{"k": invalid_value}'::json
/*
ERROR:  Token "invalid_value" is invalid.invalid input syntax for type json 

ERROR:  invalid input syntax for type json
SQL state: 22P02
Detail: Token "invalid_value" is invalid.
Character: 242
Context: JSON data, line 1: {"k": invalid_value...
*/

ちなみに null は null が返ります。

select null::json /* => null */

冒頭に書いたように、TEXT 型である以上、JSON 形式で文字列が入ってくることは保証できないため、なんとかしたいです。

`BEGIN` で変換を試みて、`EXCEPTION` で例外を拾う

ということで、いったん JSON 型への変換を試みて失敗したら null を返すような Function を作成します。


CREATE OR REPLACE FUNCTION try_cast_json(json_text text)
    RETURNS json AS 
$$
BEGIN 
	BEGIN 
		RETURN json_text::json;
	EXCEPTION WHEN OTHERS THEN
		RETURN null;
	END;
END;
$$
LANGUAGE plpgsql;

参考
PostgreSQL alternative to SQL Server’s try_cast function

::json で、input text の JSON 型への変換を試みます。

OTHERS について
query_canceled 以外の例外をキャッチすることができます。
厳密に変換への試行失敗をキャッチするなら、invalid_text_representation かな？

型を変えれば、to json 以外にも対応できると思う

テスト

JSON にならない形式で、null が返ってきていたら OK

		
SELECT 
    try_cast_json('{"k": 1234}'::text),     /* => {"k": 1234} */
    try_cast_json('{"k": "string"}'::text), /* => {"k": "string" } */
    try_cast_json('{"k": null}'::text),     /* => {"k": null} */
    try_cast_json('{"k": invalid}'::text),  /* => null */
    try_cast_json(''::text),                /* => null */
    try_cast_json('あいうえお'::text),       /* => null */
    try_cast_json(null::text)               /* => null */

さいごに

ちなみに今回、PostgreSQL で Function を作るのは初めてでした。
普段は SQL Server を使うことが圧倒的に多いです。PostgreSQL を使用しているプロジェクトでは、DB を ORM で操作することがほとんどなので、PostgreSQL で SQL を書くのはよっぽどシンプルなやつばっかだったので、SQL Server とあんま変わんないな～と思ってたんですが、この辺になると意外と結構ちがうんだな～と思いました。

参考

脚注

そもそも EAV アンチパターンの一種なんじゃないのというのはいったん置いておくことにします。 ↩︎

まとめ

JSON に変換することができない文字列を cast しようとするとエラーが発生する

BEGIN で 変換を試みて、EXCEPTION で例外を拾う

テスト

さいごに

参考

Discussion

`BEGIN` で変換を試みて、`EXCEPTION` で例外を拾う