データ型JSON列の調査

次のサンプル・データには、異常なデータを持ついくつかの行が含まれています。

{
  "id":6,
  "person" : {
      "mynumber":5,
      "myarray":[1,2,3,4]
  }
}

{
  "id":7,
  "person" : {
      "mynumber":"5",
      "myarray":["1","2","3","4"]
  }
} 

問合せを使用してそれらを見つけることができます。

sql-> SELECT * FROM JSONPersons j WHERE EXISTS j.person.mynumber;
 +----+-------------------+
 | id |      person       |
 +----+-------------------+
 |  6 | myarray           |
 |    |            1      |
 |    |            2      |
 |    |            3      |
 |    |            4      |
 |    | mynumber | 5      |
 +----+-------------------+
 |  7 | myarray           |
 |    |            1      |
 |    |            2      |
 |    |            3      |
 |    |            4      |
 |    | mynumber | 5      |
 +----+-------------------+

2 rows returned 

ただし、この2つの行には、実際には異なる型として格納された数値が含まれていることに注意してください。ID 6には整数が格納され、ID 7には文字列が格納されています。その型に基づいて行を選択できます。

sql-> SELECT * FROM JSONPersons j 
WHERE j.person.mynumber IS OF TYPE (integer);
 +----+-------------------+
 | id |      person       |
 +----+-------------------+
 |  6 | myarray           |
 |    |            1      |
 |    |            2      |
 |    |            3      |
 |    |            4      |
 |    | mynumber | 5      |
 +----+-------------------+ 

IS NOT OF TYPEを使用している場合は、ID 6を除く表のすべての行が戻されます。これは、その他すべての行では、j.person.mynumberが(整数ではない) jnullと評価されるためです。

sql-> SELECT id FROM JSONPersons j 
WHERE j.person.mynumber IS NOT OF TYPE (integer);
 +----+
 | id |
 +----+
 |  3 |
 |  2 |
 |  5 |
 |  4 |
 |  1 |
 |  7 |
 +----+

6 rows returned 

この問題を解決するには、j.person.mynumberの存在も確認します。

sql-> SELECT id from JSONPersons j WHERE EXISTS j.person.mynumber 
and j.person.mynumber IS NOT OF TYPE (integer); 
 +----+
 | id |
 +----+
 |  7 |
 +----+

1 row returned 

配列に含まれるデータの型に基づいて、型チェックを実行することもできます。この例で使用する行には、整数の配列と文字列の配列が含まれていることを思い出してください。次の文を使用して、文字列の配列のみを含む行を戻すことができます。

sql-> SELECT id, j.person.myarray FROM JSONPersons j 
WHERE j.person.myarray[] IS OF TYPE (string+);
 +----+-------------------+
 | id |      myarray      |
 +----+-------------------+
 |  7 | 1                 |
 |    | 2                 |
 |    | 3                 |
 |    | 4                 |
 +----+-------------------+

1 row returned

ここでは、WHERE句で配列フィルタ・ステップ([])を使用して、配列をシーケンスに展開しています。これにより、is-of-typeでシーケンスを反復処理し、各要素の型をチェックできます。シーケンス内のすべての要素が指定された型(この場合はstring)と一致する場合、is-of-typeはtrueを戻します。

また、問合せで+カーディナリティ修飾子を使用していることにも注意してください。これは、入力シーケンス(この場合はmyarray[])に、指定された型(string)に一致する要素が1つ以上含まれている場合にのみ、is-of-typeがtrueを戻すことを意味します。*を使用した場合は、trueが戻されるためには、0個以上の要素が指定された型に一致している必要があります。この表には異なるスキーマを持つ行が混在しているため、ID 6を除くすべての行が戻されます。

sql-> SELECT id, j.person.myarray FROM JSONPersons j 
WHERE j.person.myarray[] IS OF TYPE (string*);
 +----+-------------------+
 | id |      myarray      |
 +----+-------------------+
 |  3 | NULL              |
 +----+-------------------+
 |  5 | NULL              |
 +----+-------------------+
 |  1 | NULL              |
 +----+-------------------+
 |  7 | 1                 |
 |    | 2                 |
 |    | 3                 |
 |    | 4                 |
 +----+-------------------+
 |  4 | NULL              |
 +----+-------------------+
 |  2 | NULL              |
 +----+-------------------+

6 rows returned 

最後に、カーディナリティ修飾子をまったく指定しないと、入力シーケンスの1つのメンバーのみが、指定された型に一致する場合、is-of-typeはtrueを戻します。この例では、いずれの行も戻されません。

sql-> SELECT id, j.person.myarray FROM JSONPersons j 
WHERE j.person.myarray[] IS OF TYPE (string);

0 row returned