JSON で記述された分析データを含む大きなデータベースがあります。
不正なデータを含む行をフィルタリングしたい:
- 無効な JSON (一部の行には次のような内容が含まれています:
'{"hello": "world' - いくつかの属性は配列ではないので、省略されます
'{"products": [1,2,3]}'。'{"products": 1}'
次のようなことをしたいです:
select *
from analytics
where (is_correct_json(json::json))
and (is_array(json::json->>'products'))
どうすればそれを達成できるでしょうか?
ベストアンサー1
これは、最初から適切なデータ型を選択すると後で役立つ理由を示すもう 1 つの良い例です ;)
与えられたテキストが有効な JSON であるかどうかを確認する組み込み関数はありません。ただし、独自の関数を記述することは可能です。
create or replace function is_valid_json(p_json text)
returns boolean
as
$$
begin
return (p_json::json is not null);
exception
when others then
return false;
end;
$$
language plpgsql
immutable;
注意: 例外処理のため、これは高速ではありません。多数の無効な値に対してこれを呼び出すと、選択が大幅に遅くなります。
ただし、 と'{"products": 1}'はどちらも'{"products": [1,2,3]}'有効な JSON ドキュメントです。前者が無効であるという事実は、JSON 構文ではなく、アプリケーション ロジックに基づいています。
呼び出し時にエラーをトラップする同様の関数が必要であることを確認するにはjson_array_length()
create or replace function is_valid_json_array(p_json text, p_element text)
returns boolean
as
$$
begin
return json_array_length( p_json::json -> p_element) >= 0;
exception
when others then
return false;
end;
$$
language plpgsql
immutable;