[解決済み] Postgresで、レコードにjsonや文字列が混在している場合に、'invalid input syntax for type json'を防ぐにはどうしたらよいでしょうか。

質問

JSONとプランテキストを含むテキストカラムを持っています。 それをJSONに変換し、特定のプロパティを選択したいと思います。 例えば

user_data
_________
{"user": {"name": "jim"}}
{"user": {"name": "sally"}}
some random data string

試してみました。

select user_data::json#>'{user,name}' from users

得ることができる。

ERROR:  invalid input syntax for type json
DETAIL:  Token "some" is invalid.
CONTEXT:  JSON user_data, line 1: some...

これを防ぐことは可能なのでしょうか？

解決方法は？

無効なJSONを含む行をスキップしたい場合は、まず テスト が有効なJSONであるかどうかを確認します。これを行うには、値のパースを試み、無効な JSON 値の例外をキャッチする関数を作成します。

CREATE OR REPLACE FUNCTION is_json(input_text varchar) RETURNS boolean AS $$
  DECLARE
    maybe_json json;
  BEGIN
    BEGIN
      maybe_json := input_text;
    EXCEPTION WHEN others THEN
      RETURN FALSE;
    END;

    RETURN TRUE;
  END;
$$ LANGUAGE plpgsql IMMUTABLE;

それができたら is_json 関数の中で CASE または WHERE 節を使用して、有効な値を絞り込むことができます。

-- this can eliminate invalid values
SELECT user_data::json #> '{user,name}'
FROM users WHERE is_json(user_data);

-- or this if you want to fill will NULLs
SELECT
  CASE
    WHEN is_json(user_data)
      THEN user_data::json #> '{user,name}'
    ELSE
      NULL
  END
FROM users;