はじめに

前回のブログでは「SalesforceにおけるBig Objectの定義・作成」をご紹介いたしました。
本記事ではBig ObjectのSOQLクエリについて紹介します。

まず、Big Obejctについてもカスタムオブジェクトと同様にSOQLクエリによるレコードの参照が行えます。ですのでApexコード内で Big Obejct レコードを参照し、それをもとに同期的に処理を行うとったことが可能です。

以下は前記事で定義したBig Object、購入履歴 ( Purchase_History__b ) のレコードを参照するSOQLクエリ例です。

SELECT Contact__c,Contact__r.Name FROM Purchase_History__b WHERE Contact__c = '0035h00000JPR5U'

カスタムオブジェクトを参照する場合と同様の記載でクエリを行うことが可能です。参照項目のリレーションも同様にたどれています。

このように単純なデータの参照であればBig Objectであることをあまり意識せずを扱うことが出来ます。しかしBig ObjectのSOQLクエリには以下のような多くの制約があるため、Big Objectの導入にあたってはそのことを把握しておく必要があります。

1. 検索条件に対する制約がある
   1. 検索条件に指定できる項目がインデックスで定義された項目に限られる
   2. 検索条件で使える演算子やその使い方が限られる
2. GROUP BY や ORDER BY といったSOQLサブコマンドの多くが使えない

本記事ではこれらのBig ObjectのSOQLクエリ特有の制約について具体的なSOQLクエリとその実行結果をサンプルとして紹介していきたいと思います。

サンプルでは以前の記事で作成した「購入履歴」のオブジェクトを使っていきます。
以下がその項目定義になります。

Index順	ラベル名	API参照名	型
1	取引先責任者	Contact__c	参照関係(取引先責任者)
2	商品コード	Product_Code__c	テキスト(20)
3	購入日時	Purchase_DateTime__c	日付/時間
–	支払方法	Payment_Method__c	テキスト

※本記事執筆時のAPIバージョンは53.0になります。

検索条件に対する制約

検索条件に指定できる項目がインデックスで定義された項目に限られる

Big Objectの項目の検索はインデックス項目のみに行えます。またインデックスの順序に制約を受けます。

1. インデックス項目のみが検索可能
2. インデックス項目を複数定義した場合はインデックスの順序を飛ばして検索を行えない

それぞれ具体的にクエリを行って確認していきます。

1. インデックス項目のみが検索可能

SELECT Contact__c,Product_Code__c,Purchase_DateTime__c FROM Purchase_History__b WHERE Payment_Method__c = '現金'

この場合は以下のようにPayment_Method__cの項目ではフィルターが出来ないというエラーとなります。

[object Object]: FROM Purchase_History__b WHERE Payment_Method__c = ‘現金’ ^ ERROR at Row:1:Column:153 field ‘Payment_Method__c’ can not be filtered in a query call

インデックス項目であるContact__cで検索すると取引先責任者を参照する購入履歴レコードがクエリできました。

SELECT Contact__c,Product_Code__c,Purchase_DateTime__c FROM Purchase_History__b WHERE Contact__c = '0035h00000JPR5U'

では同様にインデックス項目である商品コードを検索してみます

SELECT Contact__c,Product_Code__c,Purchase_DateTime__c FROM Purchase_History__b WHERE Product_Code__c = '000001'

以下のように複合キーに抜けがあるというエラーとなってしまいます。

[object Object]: Filters may not have any gaps within the composite key

これが 2. インデックス項目を複数定義した場合はインデックスの順序を飛ばして検索を行えない の制約にあたるものとなります。

以下のように1番目と2番目のインデックス項目両方について検索条件に含めクエリを行ってみます。

SELECT Contact__c,Product_Code__c,Purchase_DateTime__c FROM Purchase_History__b WHERE Contact__c = '0035h00000JPR5U' AND Product_Code__c = '000001'

取引先責任者Aを参照する購入履歴レコードの内、商品コードが’000001’であるものが検索されました。
3番目のインデックス項目についても同様で以下のようにそれ以前のインデックス項目すべてを検索条件に含む必要があります。1番目と3番目のみで検索するといったような使い方はできません。

SELECT Contact__c,Product_Code__c,Purchase_DateTime__c FROM Purchase_History__b WHERE Contact__c = '0035h00000JPR5U' AND Product_Code__c = '000001' AND Purchase_DateTime__c = TODAY

複数のインデックス項目を決まった順序で結合した文字列に対して前方から検索をかけていくイメージをしてもらうと理解しやすいかもしれません。
なお参照項目がインデックス項目である場合でもリレーションをたどった検索はできません。

SELECT Contact__c,Product_Code__c,Purchase_DateTime__c FROM Purchase_History__b WHERE Contact__r.Name = 'Tanaka'

このような場合はサブクエリで検索が行えます

SELECT Contact__c,Product_Code__c,Purchase_DateTime__c FROM Purchase_History__b WHERE Contact__c IN (SELECT ID FROM Contact WHERE Name = 'Tanaka')

検索条件で使える演算子やその使い方が限られる

検索条件に使える演算子は以下に限られています。

=、IN 、<、>、<=、>=

また以下の演算子は使えません

!=、LIKE、NOT IN、EXCLUDES、INCLUDES

SELECT Contact__c,Product_Code__c,Purchase_DateTime__c FROM Purchase_History__b WHERE Contact__c = '0035h00000JPR5U' AND Product_Code__c != '000001'

SELECT Contact__c,Product_Code__c,Purchase_DateTime__c FROM Purchase_History__b WHERE Contact__c = '0035h00000JPR5U' AND Product_Code__c IN ('000002','000003')

また 複数のインデックス項目を検索している場合は範囲に対する演算子 <、>、<=、>= は最後のインデックス項目にしか適用できません

SELECT Contact__c,Product_Code__c,Purchase_DateTime__c FROM Purchase_History__b WHERE Contact__c = '0035h00000JPR5U' AND Product_Code__c = '000001' AND Purchase_DateTime__c > 2000-01-01

SELECT Contact__c,Product_Code__c,Purchase_DateTime__c FROM Purchase_History__b WHERE Contact__c = '0035h00000JPR5U' AND Product_Code__c = '000001' AND Purchase_DateTime__c > 2000-01-01 AND Payment_Date__c < 2030-01-01

※<、>、<=、>= の演算子は日付/時間型や数値型のインデックス項目にしか適用できないため文字列型の「商品コード」には適用できません。例示のため「支払日」Payment_Date__cの項目を4番目のインデックス項目扱いとしてクエリを記載しています。

また注意点として日付リテラルを使用した検索条件も<、>、<=、>= の演算子を使用しなくても範囲に対する検索のためエラーとなります。

SELECT Contact__c,Product_Code__c,Purchase_DateTime__c FROM Purchase_History__b WHERE Contact__c = '0035h00000JPR5U' AND Product_Code__c = '000001' AND Purchase_DateTime__c = LAST_MONTH AND Payment_Date__c = THIS_MONTH

同一のインデックス項目に対して範囲検索条件を複数指定することは問題ありません。

SELECT Contact__c,Product_Code__c,Purchase_DateTime__c FROM Purchase_History__b WHERE Contact__c = '0035h00000JPR5U' AND Product_Code__c = '000001' AND Purchase_DateTime__c < 2023-01-01 AND Purchase_DateTime__c > 2021-12-31

リファレンスによると IN についても最後のインデックス項目についてのみしか使えないと記載があるのですが以下のクエリは機能します。

SELECT Contact__c,Product_Code__c,Purchase_DateTime__c FROM Purchase_History__b WHERE Contact__c IN ('0035h00000JPR5U','0035h00000JPAAA') AND Product_Code__c = '000001'

SELECT Contact__c,Product_Code__c,Purchase_DateTime__c FROM Purchase_History__b WHERE Contact__c IN ('0035h00000JPR5U','0035h00000JPAAA') AND Product_Code__c = '000001' AND Purchase_DateTime__c = LAST_MONTH

ですが以下のように複数のIN演算子を組み合わせた場合にはエラーとなりました。

SELECT Contact__c,Product_Code__c,Purchase_DateTime__c FROM Purchase_History__b WHERE Contact__c IN ('0035h00000JPR5U','0035h00000JPAAA') AND Product_Code__c IN ('000001','000002')

[object Object]: Only AND is supported in the WHERE clause

以下のようなOR句を使った検索条件でも同様のエラーとなるので、原則的には最後の検索項目のみでIN 、<、>、<=、>= の演算子を使用し、前の検索項目は = で完全一致のみとするのがベターです。

SELECT Contact__c,Product_Code__c,Purchase_DateTime__c FROM Purchase_History__b WHERE (Contact__c = '0035h00000JPR5U' AND Product_Code__c = '000001') OR (Contact__c = '0035h00000JPBBB' AND Product_Code__c = '000002')

[object Object]: Only AND is supported in the WHERE clause

このように検索項目はインデックスの順序に制約されるため、このような検索に関する制約があるため、インデックスの定義については事前にユースケースをよく検討した上で定義する必要があります。例えば「購入履歴」の現在のインデックス定義ですと、昨日の購入履歴のみを全件取得するようなSOQLを行うことが出来ません。

SELECT Contact__c,Product_Code__c,Purchase_DateTime__c FROM Purchase_History__b WHERE Purchase_DateTime__c = YESTERDAY

[object Object]: Filters may not have any gaps within the composite key

こういった用途の場合は非同期SOQLのような機能やApexバッチによる集計処理などが必要となってしまいます。

※非同期SOQL : Chatter REST API のリソース。SOQLベースのコマンドによりBig Objectのデータセットに対して非同期ジョブとして集計等を行い、その結果のデータセットをカスタムオブジェクトやBig Objectのレコードとして保管できる。非同期SOQLでのSOQL文では本記事で紹介している標準のSOQLと異なりBig Objectに対してもGROUP BY などのサブコマンドが使える。アドオンの導入が必要。

GROUP BY や ORDER BY といったSOQLサブコマンドが使えない

以下のようにそれぞれエラーとなってしまいます。

レコードの並び順を指定するORDER BY

SELECT Contact__c,Product_Code__c,Purchase_DateTime__c FROM Purchase_History__b WHERE Contact__c = '0035h00000JPR5U' ORDER BY Payment_Date__c DESC

[object Object]: Contact__c = ‘0035h00000JPR5U’ ORDER BY Payment_Date__c DESC ^ ERROR at Row:1:Column:193 field ‘Payment_Date__c’ can not be sorted in a query call

集計を行うGROUP BY

SELECT Product_Code__c, Count(id) FROM Purchase_History__b WHERE Contact__c = '0035h00000JPR5U' GROUP BY Product_Code__c

[object Object]: Contact__c = ‘0035h00000JPR5U’ GROUP BY Product_Code__c ^ ERROR at Row:1:Column:106 field ‘Product_Code__c’ can not be grouped in a query call

まとめ

1. Big Objectのレコードはカスタムオブジェクトと同様にApexコードブロックや開発者コンソールからSOQLにより同期的に参照することができる。
2. しかし以下のような制約がある
   1. インデックス項目のみしか検索できない
   2. インデックス順序が後の項目を検索する場合はそれより前のインデックス項目についても検索条件を指定しなくてはならない。
   3. 検索条件としてつかえる比較演算子は=、IN 、<、>、<=、>=に限られ、またIN 、<、>、<=、>=の演算子が使えるのは指定された検索条件のインデックス項目のうち、順序がもっとも後の項目のみ
   4. クエリ結果の集計や並び替えのSOQLサブコマンドは使用できない
3. これらの制約を回避するためには非同期SOQL APIといった機能を用いる必要がある

運用にあたってSOQLによるデータの参照が想定される場合はインデックスの定義時点からそのユースケースを洗い出し、十分に検討する必要があります。インデックスの定義はBig Objectのリリース後は変更できないため、もろもろの制約を念頭におきインデックス項目と順序を定義しましょう。

また本記事では取り立てて記載はしませんでしたが、運用が続いていくに伴いBig Objectのレコード数は膨大なものになることが想定されます。Apexコードで用いるSOQLの検索条件は少量のレコードのみを返す選択的なクエリになるように十分検討する必要があるでしょう。

セールスフォースのご相談・お問い合わせ