問合せでの文字列関数の使用

様々な文字列の組込み関数があります。任意の文字列で、位置は0から始まり、長さ- 1で終了します。

例に従う場合は、「問合せを実行するサンプル・データ」を参照してサンプル・データを表示し、スクリプトを使用してテスト用のサンプル・データをロードします。このスクリプトにより、例で使用する表が作成され、表にデータがロードされます。

例に従う場合は、問合せを実行するサンプル・データを参照してサンプル・データを表示し、OCIコンソールを使用してサンプル表を作成し、JSONファイルを使用してデータをロードする方法を学習します。

substring関数

substring関数は、指定された数値の開始位置および指定された数値の部分文字列の長さに従って、指定された文字列から文字列を抽出します。

returnvalue substring (source, position [, substring_length] )

source ::= any*
position ::= integer*
substring_length ::= integer*
returnvalue ::= string

例: チケット番号が1762376407826の旅客のルーティング詳細から最初の3文字がフェッチされます。

SELECT substring(bag.baginfo.routing,0,3) AS Source
FROM baggageInfo bag
WHERE ticketNo=1762376407826

出力:

{"Source":"JFK"}

concat関数

concat関数は、すべての引数を連結し、連結された文字列を出力として表示します。

returnvalue concat (source,[source*])
source ::= any*
returnvalue ::= string

例:特定のチケット番号を持つ顧客のルーティングを、The route for passenger_name is ...というように表示します。

SELECT concat("The route for passenger ",fullName , " is ", bag.baginfo[0].routing)
FROM baggageInfo bag
WHERE ticketNo=1762376407826

出力:

{"Column_1":"The route for passenger Dierdre Amador is JFK/MAD"}

top関数およびlower関数

upperおよびlowerは、すべて大文字または小文字にそれぞれ変換する単純なファンクションです。upper関数は、文字列内のすべての文字を大文字に変換します。lower関数は、文字列内のすべての文字を小文字に変換します。

returnvalue upper (source)
returnvalue lower (source)

source ::= any*
returnvalue ::= string

例1:チケット番号が1762376407826の乗客を大文字でフェッチします。

SELECT upper(fullname) AS FULLNAME_CAPITALS
FROM BaggageInfo
WHERE ticketNo=1762376407826

出力:

{"FULLNAME_CAPITALS":"DIERDRE AMADOR"}

例2:チケット番号が1762376407826の乗客を小文字でフェッチします。

SELECT lower(fullname) AS fullname_lowercase
FROM BaggageInfo WHERE ticketNo=1762376407826

出力:

{"fullname_lowercase":"dierdre amador"}

trim関数

trim関数を使用すると、文字列から先行文字または後続文字(あるいはその両方)を切り捨てることができます。ltrim関数を使用すると、文字列の先頭の文字を切り捨てることができます。rtrim関数を使用すると、文字列の後続文字を削除できます。

returnvalue trim(source [, position [, trim_character]])

source ::= any*
position ::= "leading"|"trailing"|"both"
trim_character ::= string*
returnvalue ::= string

returnvalue ltrim(source)

returnvalue rtrim(source)
source ::= any*
returnvalue ::= string

例:チケット番号が1762350390409の旅客のルート詳細から、先頭および末尾に空白を削除します。

SELECT trim(bag.baginfo[0].routing,"trailing"," ")
FROM BaggageInfo bag
WHERE ticketNo=1762376407826

出力:

{"Column_1":"JFK/MAD"}

ltrim関数を使用した先頭のスペースの削除:

SELECT ltrim(bag.baginfo[0].routing)
FROM BaggageInfo bag
WHERE ticketNo=1762376407826

出力:

{"Column_1":"JFK/MAD"}

rtrim関数を使用した末尾のスペースの削除:

SELECT rtrim(bag.baginfo[0].routing)
FROM BaggageInfo bag
WHERE ticketNo=1762376407826

出力:

{"Column_1":"JFK/MAD"}

length関数

length関数は、文字列の長さを返します。length関数は、UTF文字セットを使用して長さを計算します。

returnvalue length(source)

source ::= any*
returnvalue ::= integer

例:チケット番号が1762350390409の旅客のフルネームの長さを調べます。

SELECT fullname, length(fullname) AS fullname_length
FROM BaggageInfo
WHERE ticketNo=1762350390409

出力:

{"fullname":"Fallon Clements","fullname_length":15}

contains関数

contains関数は、検索文字列がソース文字列の中に存在するかどうかを示します。

returnvalue contains(source, search_string)

source ::= any*
search_string ::= any*
returnvalue ::= boolean

例: ルートにSFOが含まれている乗客のフルネームをフェッチします。

SELECT fullname FROM baggageInfo bag
WHERE EXISTS bag.bagInfo[contains($element.routing,"SFO")]

出力:

{"fullname":"Michelle Payne"}
{"fullname":"Lucinda Beckman"}
{"fullname":"Henry Jenkins"}
{"fullname":"Lorenzo Phil"}
{"fullname":"Gerard Greene"}

starts_with関数およびends_with関数

starts_with関数は、ソース文字列が検索文字列から始まっているかどうかを示します。

returnvalue starts_with(source, search_string)

source ::= any*
search_string ::= any*
returnvalue ::= boolean

ends_with関数は、ソース文字列が検索文字列で終わるかどうかを示します。

returnvalue ends_with(source, search_string)

source ::= any*
search_string ::= any*
returnvalue ::= boolean

例:チケット番号が176234463813の乗客に関して、チェックインの時点から、搭乗時に荷物がスキャンされるまでにかかる時間はどのくらいですか。

SELECT $flightLeg.flightNo,
$flightLeg.actions[contains($element.actionCode, "Checkin")].actionTime AS checkinTime,
$flightLeg.actions[contains($element.actionCode, "BagTag Scan")].actionTime AS bagScanTime,
timestamp_diff(
   $flightLeg.actions[contains($element.actionCode, "Checkin")].actionTime,
   $flightLeg.actions[contains($element.actionCode, "BagTag Scan")].actionTime
) AS diff
FROM baggageinfo $s, $s.bagInfo[].flightLegs[] AS $flightLeg
WHERE ticketNo=176234463813
AND starts_with($s.bagInfo[].routing, $flightLeg.fltRouteSrc)

説明:手荷物データでは、それぞれのflightLegに1つのアクション配列があります。actions配列には3つの異なるアクションがあります。配列の最初の要素のアクション・コードは、Checkin/Offloadです。最初の区間ではアクション・コードがCheckinとなり、他の区間ではアクション・コードが中継点でのOffloadとなります。配列の2番目の要素のアクション・コードは、BagTag Scanです。前述の問合せでは、バッグ・タグ・スキャンとチェックイン時間の間のアクション時間の差異を確認します。アクション・コードがCheckinまたはBagScanの場合にのみ、contains関数を使用してアクション時間をフィルタ処理します。最初の飛行区間にのみチェックインおよび手荷物スキャンの詳細が含まれるため、starts_with関数を使用してさらにデータをフィルタ処理し、ソース・コードfltRouteSrcのみをフェッチします。

出力:

{"flightNo":"BM572","checkinTime":"2019-03-02T03:28:00Z",
"bagScanTime":"2019-03-02T04:52:00Z","diff":-5040000}

例2: 目的地がJTRである乗客のリストを確認します。

SELECT fullname FROM baggageInfo $bagInfo
WHERE ends_with($bagInfo.bagInfo[].routing, "JTR")

出力:

{"fullname":"Lucinda Beckman"}
{"fullname":"Gerard Greene"}
{"fullname":"Michelle Payne"}

index_of関数

index_of関数は、検索文字列の最初の文字(ある場合)の最初の出現時の位置を決定します。

returnvalue index_of(source, search_string [, start_position])

source ::= any*
search_string ::= any*
start_position ::= integer*
returnvalue ::= integer

様々な戻り値:

• 検索文字列の最初の文字の最初の出現時の位置を返します。位置は、文字列の開始位置(ゼロ)からの相対位置です。

• search_stringがソースに存在しない場合は-1を返します。

• search_stringの長さが0である場合、ソースの値に対して0を返します。

• 引数がNULLの場合はNULLを返します。

• 引数が空の順序であるか、複数の項目がある順序の場合はNULLを返します。

• start_position引数が整数でない場合はエラーを返します。

例1:チケット番号が1762320569757の乗客について、「- 」が最初のレッグの推定到着時間に検出される位置を特定します。

SELECT index_of(bag.baginfo.flightLegs[0].estimatedArrival,"-")
FROM BaggageInfo bag
WHERE ticketNo=1762320569757

出力:

{"Column_1":4}

例2:チケット番号が1762320569757の乗客について、「/」が最初のレッグのルーティングで検出される位置を特定します。これは、チケット番号が1762320569757の乗客に関するソース・ポイントについて、文字数を特定するのに役立ちます。

SELECT index_of(bag.baginfo.routing,"/")
FROM BaggageInfo bag
WHERE ticketNo=1762320569757

出力:

"Column_1":3}

置換関数

replace関数は、ソースでの検索文字列のすべての出現を置換文字列に置き換えて返します。

returnvalue replace(source, search_string [, replacement_string])

source ::= any*
search_string ::= any*
replacement_string ::= any*
returnvalue ::= string

例: チケット番号が1762320569757の乗客のソースの場所を、SFOからSOFに置き換えます。

SELECT replace(bag.bagInfo[0].routing,"SFO","SOF")
FROM baggageInfo bag
WHERE ticketNo=1762320569757

出力:

{"Column_1":"SOF/IST/ATH/JTR"}

例2:乗客名の二重引用符を一引用符で置き換えます。

乗客の名前に二重引用符が含まれている可能性がある場合は、replace関数を使用して、二重引用符を一引用符に変更できます。

SELECT fullname,
replace(fullname, """, "'") as new_fullname
FROM BaggageInfo bag

reverse関数

reverse関数は、ソース文字列の文字を、文字列の最後の文字で始まる逆の順序で返します。

returnvalue reverse(source)

source ::= any*
returnvalue ::= string

例:フルネームを表示し、チケット番号が1762330498104の乗客のフルネームを逆にする。

SELECT fullname, reverse(fullname)
FROM baggageInfo
WHERE ticketNo=1762330498104

出力:

{"fullname":"Michelle Payne","Column_2":"enyaP ellehciM"}

QueryRequest APIの使用例

QueryRequest APIを使用し、SQL関数を適用してNoSQL表からデータをフェッチできます。

• Java
• Python
• Go
• Node.js
• C#

問合せを実行するには、NoSQLHandle.query() APIを使用します。

こちらにあるサンプルの中にフル・コードSQLFunctions.javaをダウンロードします。

 //Fetch rows from the table
private static void fetchRows(NoSQLHandle handle,String sqlstmt) throws Exception {
   try (
      QueryRequest queryRequest = new QueryRequest().setStatement(sqlstmt);
      QueryIterableResult results = handle.queryIterable(queryRequest)){
      for (MapValue res : results) {
         System.out.println("\t" + res);
      }
   }
}

String string_func1="SELECT substring(bag.baginfo.routing,0,3) AS Source FROM baggageInfo bag WHERE ticketNo=1762376407826";
System.out.println("Using substring function ");
fetchRows(handle,string_func1);
String string_func2="SELECT fullname, length(fullname) AS fullname_length FROM BaggageInfo WHERE ticketNo=1762320369957";
System.out.println("Using length function ");
fetchRows(handle,string_func2);
String string_func3="SELECT fullname FROM baggageInfo bag WHERE EXISTS bag.bagInfo[contains($element.routing,\"SFO\")]";
System.out.println("Using contains function ");
fetchRows(handle,string_func3);

問合せを実行するには、borneo.NoSQLHandle.query()メソッドを使用します。

こちらにあるサンプル中からフル・コードSQLFunctions.pyをダウンロードします。

# Fetch data from the table
def fetch_data(handle,sqlstmt):
   request = QueryRequest().set_statement(sqlstmt)
   print('Query results for: ' + sqlstmt)
   result = handle.query(request)
   for r in result.get_results():
      print('\t' + str(r))

string_func1 = '''SELECT substring(bag.baginfo.routing,0,3) AS Source FROM baggageInfo bag
                    WHERE ticketNo=1762376407826'''
print('Using substring function:')
fetch_data(handle,string_func1)

string_func2 = '''SELECT fullname, length(fullname) AS fullname_length FROM BaggageInfo
                    WHERE ticketNo=1762320369957'''
print('Using length function:')
fetch_data(handle,string_func2)

string_func3 = '''SELECT fullname FROM baggageInfo bag WHERE
                    EXISTS bag.bagInfo[contains($element.routing,"SFO")]'''
print('Using contains function:')
fetch_data(handle,string_func3)

問合せを実行するには、Client.Query関数を使用します。

こちらにあるサンプル中からフル・コードSQLFunctions.goをダウンロードします。

 //fetch data from the table
func fetchData(client *nosqldb.Client, err error, tableName string, querystmt string)(){
   prepReq := &nosqldb.PrepareRequest{
		Statement: querystmt,
   }
   prepRes, err := client.Prepare(prepReq)
   if err != nil {
      fmt.Printf("Prepare failed: %v\n", err)
      return
   }
   queryReq := &nosqldb.QueryRequest{
		 PreparedStatement: &prepRes.PreparedStatement,   }
   var results []*types.MapValue
   for {
      queryRes, err := client.Query(queryReq)
      if err != nil {
         fmt.Printf("Query failed: %v\n", err)
  	return
      }
      res, err := queryRes.GetResults()
      if err != nil {
         fmt.Printf("GetResults() failed: %v\n", err)
  	return
      }
      results = append(results, res...)
      if queryReq.IsDone() {
         break
      }
   }
   for i, r := range results {
      fmt.Printf("\t%d: %s\n", i+1, jsonutil.AsJSON(r.Map()))
   }
}

string_func1 := `SELECT substring(bag.baginfo.routing,0,3) AS Source FROM baggageInfo bag
                    WHERE ticketNo=1762376407826`
fmt.Printf("Using substring function:\n")
fetchData(client, err,tableName,string_func1)

string_func2 := `SELECT fullname, length(fullname) AS fullname_length FROM BaggageInfo
                    WHERE ticketNo=1762320369957`
fmt.Printf("Using length function:\n")
fetchData(client, err,tableName,string_func2)

string_func3 := `SELECT fullname FROM baggageInfo bag WHERE
                    EXISTS bag.bagInfo[contains($element.routing,"SFO")]`
fmt.Printf("Using contains function:\n")
fetchData(client, err,tableName,string_func3)

問合せを実行するには、queryメソッドを使用します。

JavaScript: こちらのサンプルの中からフル・コードSQLFunctions.jsをダウンロードします。

  //fetches data from the table
async function fetchData(handle,querystmt) {
   const opt = {};
   try {
      do {
         const result = await handle.query(querystmt, opt);
         for(let row of result.rows) {
            console.log('  %O', row);
         }
         opt.continuationKey = result.continuationKey;
      } while(opt.continuationKey);
   } catch(error) {
      console.error('  Error: ' + error.message);
   }
}

TypeScript: こちらのサンプルの中からフル・コードSQLFunctions.tsをダウンロードします。

interface StreamInt {
   acct_Id: Integer;
   profile_name: String;
   account_expiry: TIMESTAMP;
   acct_data: JSON;
}

/* fetches data from the table */
async function fetchData(handle: NoSQLClient,querystmt: any) {
   const opt = {};
   try {
      do {
         const result = await handle.query<StreamInt>(querystmt, opt);
         for(let row of result.rows) {
            console.log('  %O', row);
         }
         opt.continuationKey = result.continuationKey;
      } while(opt.continuationKey);
   } catch(error) {
      console.error('  Error: ' + error.message);
   }
}

const string_func1 = `SELECT substring(bag.baginfo.routing,0,3) AS Source FROM baggageInfo bag
                      WHERE ticketNo=1762376407826`
console.log("Using substring function:");
await fetchData(handle,string_func1);

const string_func2 = `SELECT fullname, length(fullname) AS fullname_length FROM BaggageInfo
                      WHERE ticketNo=1762320369957`
console.log("Using length function");
await fetchData(handle,string_func2);

const string_func3 = `SELECT fullname FROM baggageInfo bag WHERE
                      EXISTS bag.bagInfo[contains($element.routing,"SFO")]`
console.log("Using contains function");
await fetchData(handle,string_func3);

問合せを実行するには、QueryAsyncメソッドをコールするか、GetQueryAsyncEnumerableメソッドをコールして、結果の非同期列挙可能性に対して反復処理します。

こちらにあるサンプルの中にフル・コードSQLFunctions.csをダウンロードします。

private static async Task fetchData(NoSQLClient client,String querystmt){
   var queryEnumerable = client.GetQueryAsyncEnumerable(querystmt);
   await DoQuery(queryEnumerable);
}

private static async Task DoQuery(IAsyncEnumerable<QueryResult<RecordValue>> queryEnumerable){
   Console.WriteLine("  Query results:");
   await foreach (var result in queryEnumerable) {
      foreach (var row in result.Rows)
      {
         Console.WriteLine();
         Console.WriteLine(row.ToJsonString());
      }
   }
}

private const string string_func1 =@"SELECT substring(bag.baginfo.routing,0,3) AS Source FROM baggageInfo bag
                                           WHERE ticketNo=1762376407826" ;
Console.WriteLine("\nUsing substring function!");
await fetchData(client,string_func1);

private const string string_func2 =@"SELECT fullname, length(fullname) AS fullname_length FROM BaggageInfo
                                           WHERE ticketNo=1762320369957";
Console.WriteLine("\nUsing length function!");
await fetchData(client,string_func2);


private const string string_func3 =@"SELECT fullname FROM baggageInfo bag WHERE
                                           EXISTS bag.bagInfo[contains($element.routing,""SFO"")]";
Console.WriteLine("\nUsing contains function!");
await fetchData(client,string_func3);

関連トピック

• 表の作成
• データの追加