この節では次のことを説明します。
JSONデータを処理、生成する関数と演算子
SQL/JSONパス言語
SQL環境内のJSONデータ型にネイティブサポートを提供するために、PostgreSQLSQL/JSONデータモデルを実装しています。 このモデルは、一連の項目で構成されます。 各項目は、SQLスカラ値、追加のSQL/JSON NULL値、およびJSON配列とオブジェクトを使用する複合データ構造を保持できます。 モデルは、JSON規格RFC 7159で暗黙的に指定されているデータモデルを形式化したものです。
SQL/JSONでは、トランザクションをサポートをしながら、通常のSQLデータと一緒にJSONデータをハンドルすることができます。以下のものが含まれます:
JSONデータをデータベースにアップロードし、それを文字またはバイナリ文字列として通常のSQL列に保存します。
リレーショナルデータからJSONオブジェクトと配列を生成します。
SQL/JSON問い合わせ関数およびSQL/JSONパス言語式を使用してJSONデータを照会します。
SQL/JSON標準を更に学ぶためには、[sqltr-19075-6]をご覧ください。 PostgreSQLでサポートされているJSON型の詳細に関しては、8.14をご覧ください。
表 9.45にJSONデータ型(8.14を参照)で使用可能な演算子を示します。
加えて表 9.1で示す通常の比較演算子がjsonb
で利用できますが、json
では利用できません。
比較演算子は8.14.4で概要が示されているように示すBツリー操作用の順序付け規則にしたがいます。
レコードの値をJSONに集約するjson_agg
集約関数、値の対をJSONオブジェクトに集約するjson_object_agg
集約関数、およびそれらのjsonb
版のjsonb_agg
とjsonb_object_agg
については9.21も参照して下さい。
表9.45 json
とjsonb
演算子
演算子 説明 例 |
---|
JSON配列の
|
与えられたキーでJSONオブジェクトフィールドを取り出します。
|
JSON配列の
|
与えられたキーでJSONオブジェクトフィールドを
|
指定したパスにおけるJSONの副オブジェクトを取り出します。パス要素はフィールドキーあるいは配列のインデックスでも構いません。
|
指定したパスにおけるJSONの副オブジェクトを
|
JSON入力が要求と一致する正しい構造をしていなければ、フィールド/要素/パス抽出演算子は失敗するのではなくNULLを返します。例えばそのような要素が存在しない場合です。
ほかにjsonb
だけで利用可能な演算子もいくつか存在します。
それらを表 9.46に示します。
8.14.4には、インデックス付されたjsonb
データを効率的に検索するためにこれらの演算子をどのように利用できるかについて書いてあります。
表9.46 追加jsonb
演算子
演算子 説明 例 |
---|
最初のJSON値は二番目を含んでいるか? (包含の詳細は8.14.3を参照してください。)
|
最初のJSON値は二番目に含まれているか?
|
そのテキスト文字列はトップレベルのキーあるいは配列要素としてJSON値中に存在しているか?
|
テキスト配列中のどれかの文字列がトップレベルのキーあるいは配列要素として存在しているか?
|
テキスト配列のすべての文字列がトップレベルのキーあるいは配列要素として存在しているか?
|
2つの
一つの要素を持つとして配列を他の配列に追加するには、例のように配列の追加のレイヤ中に含めてください。
|
キー(及びその値)をJSONオブジェクトから削除します。あるいはマッチする文字列値をJSON配列から削除します。
|
左のオペランドからマッチするすべてのキーあるいは配列要素を削除します。
|
指定したインデックス(負の整数は最後から数えます)の配列要素を削除します。 JSON値が配列でなければエラーが生じます。
|
指定パスのフィールドあるいは配列要素を削除します。パス要素はフィールドキーあるいは配列インデックスが指定できます。
|
JSONパスは指定したJSON値の要素を返すか?
|
指定したJSON値に対するJSONパス述語チェックの結果を返します。
結果の最初の項目だけが考慮されます。
結果が論理値でなければ
|
jsonpath
演算子の@?
および@@
演算子は以下のエラーを抑止します。
オブジェクトフィールドあるいは配列要素の欠如、期待しないJSON要素型、日付時刻及び数値エラー。
以下に示すjsonpath
関連の関数もこれらのエラーを抑止するようにすることもできます。
この振る舞いは、異なる構造のJSON文書集合を検索する際に役に立つかも知れません。
表 9.47に、json
値およびjsonb
値を作成するために利用可能な関数を示します。
このテーブルの一部の関数は、返されるデータ型を指定するRETURNING
句を持っています。
これはjson
、jsonb
、bytea
、文字列型(text
、char
、varchar
)、あるいはjson
からその型へのキャストがある型のいずれかでなければなりません。
デフォルトではjson
型が返されます。
表9.47 JSON作成関数
関数 説明 例 |
---|
SQL値を
|
SQL配列をJSON配列に変換します。
追加の論理引数が真であるときに改行がトップレベルの配列要素の間に加えられる以外は、その振る舞いは
|
JSON配列を、一連の
|
SQL複合値をJSONオブジェクトに変換します。
追加の論理引数が真であるときに改行がトップレベルの配列要素の間に加えられる以外は、その振る舞いは
|
異なる型から構成される可能性のあるJSON配列をvariadic引数リストから作成します。
各々の引数は
|
variadic引数リストからJSONオブジェクトを作成します。
慣例により引数リストは代替キーと値が交互に並んだものです。
キー引数はテキストに強制的に変換されます。
値引数は
|
指定されたすべてのキー/値ペアのJSONオブジェクトを構築します。
キー/値ペアが指定されていない場合は、空のオブジェクトを構築します。
|
テキスト配列からJSONオブジェクトを作成します。 配列は、偶数個の要素からなる1次元(キー/値の対が交互に並んでいるものと扱われます)あるいは内側の配列が2つの要素を持つ2次元(2つの要素がキー/値のペアとして扱われます)のいずれかでなければなりません。 すべての値はJSON文字列に変換されます。
|
この形の
|
表 9.48には、JSONをテストするためのSQL/JSON機能の詳細が記載されています。
表9.48 SQL/JSONテスト用関数
表 9.49にjson
とjsonb
値を処理するのに使える関数を示します。
表9.49 JSON処理関数
関数 説明 例 |
---|
トップレベルのJSON配列をJSON値の集合に展開します。
value ----------- 1 true [2,false]
|
トップレベルのJSON配列を
value ----------- foo bar
|
トップレベルのJSON配列の要素数を返します。
|
トップレベルのJSONオブジェクトをキー/値のペアの集合に展開します。
key | value -----+------- a | "foo" b | "bar"
|
トップレベルのJSONオブジェクトをキー/値のペアの集合に展開します。
返り値の
key | value -----+------- a | foo b | bar
|
指定したパスにおけるJSONの副オブジェクトを取り出します。
(これは
|
指定したパスにおけるJSONの副オブジェクトを
|
トップレベルのJSONオブジェクト中のキーの集合を返します。
json_object_keys ------------------ f1 f2
|
トップレベルのJSONオブジェクトを JSON値を出力列のSQL型に変換する際に以下のルールが順に適用されます。
これらの関数の例ではJSON定数を使用していますが、典型的な使用法はその
a | b | c ---+-----------+------------- 1 | {2,"a b"} | (4,"a b c")
|
トップレベルのJSONオブジェクトを
a | b ---+--- 1 | 2 3 | 4
|
トップレベルのJSONオブジェクトを
a | b | c | d | r ---+---------+---------+---+--------------- 1 | [1,2,3] | {1,2,3} | | (123,"a b c")
|
トップレベルのJSON配列を
a | b ---+----- 1 | foo 2 |
|
|
|
|
与えられたJSON値からNULLを持つオブジェクトフィールドをすべて削除します。 オブジェクトフィールドではないNULL値は変わりません。
|
JSONパスが指定したJSON値に対して項目を返すかどうかをチェックします。
|
指定したJSON値のJSONパス述語チェックの結果を返します。
結果の最初の項目だけが考慮されます。
結果がBooleanでないなら、
|
JSON値に対するJSONパスによって返されるすべてのJSON項目を返します。
オプションの
jsonb_path_query ------------------ 2 3 4
|
JSON値に対するJSONパスによって返されるすべてのJSON項目をJSON配列として返します。
オプションの
|
JSON値に対するJSONパスによって返される最初のJSON項目を返します。
結果がなければ
|
これらの関数は、時間帯を考慮する日時値の比較をサポートすることを除いて、上で述べた、
|
与えられたJSON値を整形されたインデント付きテキストに変換します。
[ { "f1": 1, "f2": null }, 2 ]
|
トップレベルのJSON値の型をテキスト文字列として返します。
可能な型は次のとおりです。
|
SQL/JSONパス式は、XMLへのSQLアクセスで使用されるXPath同様、JSONデータから取り出す項目を指定します。
PostgreSQLではパス式はjsonpath
データ型として実装されており、8.14.7で説明されているすべての要素を使うことができます。
JSON問い合わせ関数と演算子は与えられたパス式をpath engineに渡して評価します。 式が問い合わせ対象のJSONデータにマッチすれば、関連するSQL/JSON項目が返却されます。 パス式はSQL/JSONパス言語で書かれ、算術式と関数を含むことができます。
パス式はjsonpath
データ型で認められた一連の要素からなります。
パス式は通常左から右へと評価されますが、括弧を使って演算の順序を変更することができます。
評価が成功すれば、一連のJSON項目が生成され、評価結果が指定した計算を完了したJSON問い合わせ関数に戻されます。
問い合わせ対象(context item)のJSONデータを参照するには、パス式内で$
値を使います。
複数のアクセサ演算子をその後に記述することもできます。
それによってJSON構造をレベル順に訪れて文脈項目の副項目の内容を取り出します。
後続の個々の演算子はその前の評価段階の結果を処理します。
たとえば、次のようなパースしたいGPSトラッカーからのJSONデータがあるとします。
{ "track": { "segments": [ { "location": [ 47.763, 13.4034 ], "start time": "2018-10-14 10:05:14", "HR": 73 }, { "location": [ 47.706, 13.2635 ], "start time": "2018-10-14 10:39:21", "HR": 135 } ] } }
存在するトラックセグメントを取り出すには、.
アクセサ演算子を使用して、周辺のJSONオブジェクトを下っていく必要があります。
key
$.track.segments
配列の内容を取り出すには、典型的には[*]
演算子を使います。
たとえば次のパスはすべての存在するトラックセグメントの位置座標を返します。
$.track.segments[*].location
最初のセグメントの座標だけを返すには、[]
アクセサ演算子の中で対応する添え字を指定することができます。
JSON配列インデックスは0スタートであることに注意してください。
$.track.segments[0].location
各段階でのパス評価結果は9.16.2.2に列挙されている一つ以上のjsonpath
演算子とメソッドで処理することができます。
各々のメソッド名の前にピリオドを付けなければなりません。
たとえば配列の大きさを得ることができます。
$.track.segments.size()
パス式内のjsonpath
演算子とメソッドを使用する他の例については以下の9.16.2.2を参照してください。
パスを定義する際にはSQLのWHERE
節のように働く一つ以上のフィルター式が利用できます。
フィルター式は疑問符で始まり、カッコ内に条件を記述します。
? (condition
)
フィルター式はそれを適用するパス評価段階の直後に指定しなければなりません。
この段階の結果は、指定した条件を満たす項目だけが含まれるようにフィルターされます。
SQL/JSONは3値論理を定義しており、条件はtrue
、false
、unknown
のどれかです。
unknown
は値はSQLのNULL
と同じ役割を果たし、is unknown
述語で評価できます。
その後の評価段階ではtrue
を返すフィルター式に対応する項目だけが使われます。
フィルター式内で利用できる関数と演算子は表 9.51にリストされています。
フィルター式内では、フィルターする必要のある値は@
変数で示します。(つまり以前のパスステップの結果の一つです。)
コンポーネント項目を取得するためにアクセサ演算子を@
の後に記述することができます。
たとえば130より高いすべての心拍数を取り出したいとします。次の式を使ってそれを得ることができます。
$.track.segments[*].HR ? (@ > 130)
そうした値を持つセグメントの開始時刻を得たい場合は、開始時刻を返す前に無関係のセグメントを取り除く必要があります。 そうすることにより前の段階にフィルター式が適用されるので、その条件で適用されるパスは異なります。
$.track.segments[*] ? (@.HR > 130)."start time"
必要なら複数のフィルター式を順に使用することができます。 たとえば次の式は指定した座標と高い心拍数値を持つ位置を持つすべてのセグメントを選択します。
$.track.segments[*] ? (@.location[1] < 13.4) ? (@.HR > 130)."start time"
異なる入れ子レベルに対してフィルター式を適用することもできます。 次の例では、まず位置ですべてのセグメントをフィルターし、もしあれば高い心拍数値を返します。
$.track.segments[*] ? (@.location[1] < 13.4).HR ? (@ > 130)
フィルター式をお互いに入れ子にすることもできます。
$.track ? (exists(@.segments[*] ? (@.HR > 130))).segments.size()
この式は高い心拍数値を含むトラックがあればそのすべてのサイズを返します。もしなければ空のシーケンスが返ります。
PostgreSQLのSQL/JSONパス言語の実装はSQL/JSON標準と次の点が異なります。
SQL/JSON標準ではフィルター内でのみ述語が使えますが、パス式はBoolean述語でも構いません。
これは@@
演算子を実装するために必要です。
たとえば、次のjsonpath
式はPostgreSQLでは有効です。
$.track.segments[*].HR < 70
9.16.2.3で述べるように、like_regex
フィルターで使用される正規表現パターンの解釈には些細な違いがあります。
JSONデータを問い合わせる際、パス式は実際のJSONデータ構造に一致しないかも知れません。 存在しないオブジェクトのメンバあるいは配列要素にアクセスしようとすると、構造上のエラーとなります。 SQL/JSONパス式には構造上のエラーを扱うための2つのモードがあります。
非厳密(lax)モード(デフォルト)— パスエンジンは指定したパスを問い合わせデータに暗黙的に適合させます。 構造上のエラーは抑止され、空のSQL/JSONシーケンスへと変換されます。
厳密(strict)モード — 構造上のエラーがあるとエラーが発生します。
非厳密モードは、JSONデータが期待されるスキーマに沿わないときにJSON文書構造とパス式のマッチングを助けます。 あるオペランドが操作の要件に合わないときにはそれをSQL/JSON配列にまとめたり、あるいは操作を行う前にそれをSQL/JSONシーケンスに展開することもできます。 また非厳密モードにおいては、比較演算子は自動的にオペランドを展開し、SQL/JSON配列をそのまま比較することができます。 大きさ1の配列はその単独要素と同じものとして扱われます。 自動展開は以下の場合にのみ行われません。
それぞれ配列の型、要素数を返すtype()
、size()
をパス式が含む。
問い合わせ対象のJSONデータが入れ子の配列を含む。 この場合はもっとも外側の配列のみが展開され、内側の配列は変わりません。 ですから、それぞれの評価段階において1レベルのみに暗黙的な展開が行われます。
たとえば、上述のGPSデータに問い合わせする際、非厳密モードでは配列のセグメントを含んでいることを抽象化できます。
lax $.track.segments.location
厳密モードでは、指定したパスはSQL/JSON項目を返す問い合わせ対象のJSON文書の構造に正確に一致していなければなりません。ですから、このパス式を使うとエラーになります。
非厳密モードと同じ結果を得るためには、segments
配列を明示的に展開する必要があります。
strict $.track.segments[*].location
.**
アクセサを非厳密モードで使うと驚くべき結果をもたらすことがあります。
たとえば、次の問い合わせはすべてのHR
値を2回検索します。
lax $.**.HR
これは.**
アクセサがsegments
とその各々の要素の両方を検索するからです。
一方、.HR
アクセサは非厳密モードでは自動的に配列を展開します。
予期しない結果を避けるには、.**
アクセサを厳密モードでのみ使うことをお勧めします。
次の問い合わせはHR
の各値を一度だけ検索します。
strict $.**.HR
表 9.50にjsonpath
で利用可能な演算子とメソッドを示します。
単項演算子とメソッドは以前のパスステップから生じた複数の値に適用できますが、二項演算子(加算など)は単一の値にしか適用できないことに注意してください。
表9.50 jsonpath
演算子とメソッド
演算子/メソッド 説明 例 |
---|
加算
|
単項のプラス(演算なし)。加算と違って、複数の値に渡って適用できます。
|
減算
|
負符号。減算と違って、複数の値に渡って適用できます。
|
乗算
|
除算
|
剰余(残り)
|
JSON項目の型(
|
JSON項目の大きさ(配列の要素数。配列でなければ1)
|
JSON数値あるいは文字列から変換した概算の浮動小数点数
|
引数より大きいか等しく、与えられた数に最も近い整数
|
引数より小さいか等しく、与えられた数に最も近い整数
|
与えられた数の絶対値
|
文字列から変換した日時値
|
指定の
|
以下の3つのフィールドを含むオブジェクトの配列で表現したオブジェクトのキー/値ペア。
|
datetime()
とdatetime(
の結果型はtemplate
)date
、timetz
、time
、timestamptz
、あるいはtimestamp
です。
両方のメソッドは結果型を動的に決定します。
datetime()
メソッドは入力文字列をdate
、timetz
、time
、timestamptz
、timestamp
のISO形式に対して順にマッチを試みます。
最初にマッチした形式で停止し、関連するデータ型を出力します。
datetime(
メソッドは与えられたテンプレート文字列にあるフィールドに従って結果型を決定します。
template
)
datetime()
とdatetime(
はtemplate
)to_timestamp
SQL関数と同じ解析ルール(参照9.8)を用いますが、3つの例外があります。
一番目に、これらのメソッドは一致しないテンプレートパターンを許容しません。二番目に次の区切り文字のみを許容します。負符号、ピリオド、斜線(スラッシュ)、カンマ、アポストロフィー、セミコロン、コロン、空白、です。
三番目にテンプレート文字列中の区切り文字は正確に入力文字列と一致しなければなりません。
異なる日時型の比較が必要なら、暗黙的なキャストが適用されます。
date
値はtimestamp
あるいはtimestamptz
にキャストできます。
timestamp
はtimestamptz
に、time
はtimetz
にキャストできます。
しかし、これらの変換の最初のものは現在のTimeZone設定に依存します。ですから時間帯を認識するjsonpath
関数中でのみ実行可能です。
表 9.51に利用可能なフィルター式要素を示します。
表9.51 jsonpath
フィルター式要素
述語/値 説明 例 |
---|
等値比較(これと他の比較演算子はすべてのJSONスカラー値で使えます)
|
非等値比較
|
未満比較
|
以下比較
|
より大きい比較
|
以上比較
|
JSON定数
|
JSON定数
|
JSON定数
|
論理AND
|
論理OR
|
論理NOT
|
論理条件が
|
最初のオペランドが2番目のオペランドで与えられる正規表現にマッチするかどうか検査します。
オプションで
|
2番目の文字列が1番目のオペランドの最初の部分文字列かどうかを検査します。
|
パス式が少なくとも一つのSQL/JSON項目とマッチするかどうかを検査します。
パス式がエラーとなる場合は
|
SQL/JSONパス式ではlike_regex
フィルターを使ってテキストを正規表現にマッチさせることができます。
たとえば、次のSQL/JSONパス式問い合わせは、英語の母音で始まる配列内のすべての文字列に大文字小文字を無視してマッチするでしょう。
$[*] ? (@ like_regex "^[aeiou]" flag "i")
オプションのflag
文字列は一つ以上の文字を含むことができます。
i
は大文字小文字を無視したマッチ、m
は^
と$
で改行にマッチ、s
は.
が改行にマッチ、q
はパターン全体を参照します。(振る舞いを単純な部分文字列マッチとします)
SQL/JSON標準は正規表現の定義を、XQuery標準を使用するLIKE_REGEX
演算子から借りています。
PostgreSQLは今の所LIKE_REGEX
演算子をサポートしていません。
ですから、like_regex
フィルターは9.7.3で説明されているPOSIX正規表現で実装されています。
このことにより、9.7.3.8で列挙されているSQL/JSON標準の振る舞いとの小さな違いが生じます。
しかし、ここで述べているフラグ文字の非互換性はSQL/JSONには適用されないことに注意してください。SQL/JSONは、XQueryのフラグ文字をPOSIXエンジンが期待するのと一致するように解釈するからです。
like_regex
のパターン引数は8.14.7で説明されているルールにしたがって書かれたJSONパス文字列リテラルであることに注意してください。
これは、正規表現で使用するすべてのバックスラッシュを二重に書かなければならないことを意味します。
たとえば、数字のみを含むroot文書の文字列値にマッチさせるには以下のようにします。
$.* ? (@ like_regex "^\\d+$")