JSON_ARRAY (SQL)
構文
JSON_ARRAY(expression [,expression][,...]
[NULL ON NULL | ABSENT ON NULL])
説明
JSON_ARRAY は式または (より一般的には) コンマ区切りの式リストを取得して、これらの値を含む JSON 配列を返します。JSON_ARRAY は、SELECT 文で他のタイプの選択項目と組み合わせることができます。JSON_ARRAY は、WHERE 節など、SQL 関数を使用できる他の場所に指定できます。
返される JSON 配列の形式は、以下のとおりです。
[ element1 , element2 , element3 ]
JSON_ARRAY は、文字列 (二重引用符で囲まれます) または数字として、それぞれの配列要素値を返します。数字はキャノニック形式で返されます。数値文字列は、リテラルとして、二重引用符で囲まれて返されます。その他のデータ・タイプ (日付、$List など) はすべて文字列として返されます。
JSON_ARRAY では、テーブル内のすべてのフィールドを指定するための方法としてアスタリスク (*) 構文を使用することはできません。COUNT(*) 集約関数は使用できます。
返される JSON 配列の列には、Expression (既定) というラベルが付けられます。JSON_ARRAY には列のエイリアスを指定できます。
選択モードおよび照合
現在の %SelectMode プロパティによって、返される JSON 配列値のフォーマットが決まります。選択モードを変更することによって、すべての日付要素および %List 要素が、その選択モードのフォーマットの文字列として JSON 配列に組み込まれます。
フォーマット変換関数 (%EXTERNAL、%INTERNAL、%ODBCIN、%ODBCOUT) を JSON_ARRAY 内の個別のフィールド名に適用することによって、現在の選択モードをオーバーライドできます。フォーマット変換関数を JSON_ARRAY に適用しても、JSON 配列の要素は文字列であるため、影響はありません。
照合関数を JSON_ARRAY 内の個別のフィールド名または JSON_ARRAY 全体に適用できます。
-
JSON_ARRAY に割り当てた照合関数は JSON 配列のフォーマットの後に照合を適用します。したがって、%SQLUPPER(JSON_ARRAY(f1,f2)) は、すべての JSON 配列の要素の値を大文字に変換します。%SQLUPPER(JSON_ARRAY(f1,f2)) は、配列の要素の前ではなく、JSON 配列の前にスペースを挿入します。そのため、数字は文字列として解析されません。
-
JSON_ARRAY 内の要素に適用した照合関数はその照合を適用します。そのため、JSON_ARRAY('Abc',%SQLUPPER('Abc')) は ["Abc"," ABC"] を返し (先頭のスペースに注意)、JSON_ARRAY(007,%SQLSTRING(007)) は [7," 7"] を返します。%SQLUPPER は、値の前にスペースを挿入するので、一般に、LCASE や UCASE などのケース変換関数を指定することが推奨されます。照合は要素と配列全体の両方に適用できます。%SQLUPPER(JSON_ARRAY('Abc',%SQLSTRING('Abc'))) は ["ABC"," ABC"] を返します。
ABSENT ON NULL
オプションの ABSENT ON NULL キーワード句を指定すると、NULL (または NULL リテラル) の列値は、JSON 配列に含まれなくなります。JSON 配列にプレースホルダは含まれません。この結果、JSON 配列に含まれる要素の数が違ってくる可能性があります。例えば、以下のプログラムが返す JSON 配列では、一部のレコードは 3 番目の配列要素は Age になり、他のレコードでは 3 番目の要素は FavoriteColors になります。
SELECT JSON_ARRAY(%ID,Name,FavoriteColors,Age ABSENT ON NULL) FROM Sample.Person
キーワード句を指定しない場合、既定は NULL ON NULL になり、NULL は null という単語で (引用符による区切りなしで)、コンマ区切りの配列要素として表示されます。したがって、JSON_ARRAY 関数から返される JSON 配列はすべて、同じ数の配列要素を持つことになります。
引数
expression
式、または式のコンマ区切りのリスト。これらの式には、列名、集約関数、算術式、リテラル、およびリテラル NULL を含めることができます。
ABSENT ON NULL/NULL ON NULL
返された JSON 配列での NULL 値の表現方法を指定するキーワード句 (オプション)。NULL ON NULL (既定) は、NULL (存在しない) データを null という言葉 (引用符なし) で表します。ABSENT ON NULL は、NULL データを JSON 配列から省略します。プレースホルダのコンマは残しません。このキーワード句は、空の文字列値に影響を与えません。
例
以下の例は、JSON_ARRAY を適用して、コンマ区切りのフィールド値リストを持つ JSON 配列をフォーマットします。
SELECT TOP 3 JSON_ARRAY(%ID,Name,Age,Home_State) FROM Sample.Person
以下の例は、JSON_ARRAY を適用して、Name フィールド値を含む単一の要素を持つ JSON 配列をフォーマットします。
SELECT TOP 3 JSON_ARRAY(Name) FROM Sample.Person
以下の例は、JSON_ARRAY を適用して、リテラルおよびフィールド値を含む JSON 配列をフォーマットします。
SELECT TOP 3 JSON_ARRAY('Employee from',%TABLENAME,Name,SSN) FROM Sample.Employee
以下の例は、JSON_ARRAY を適用して、NULL およびフィールド値を含む JSON 配列をフォーマットします。
SELECT JSON_ARRAY(Name,FavoriteColors) FROM Sample.Person
WHERE Name %STARTSWITH 'S'
以下の例は、JSON_ARRAY を適用して、結合されたテーブルのフィールド値を含む JSON 配列をフォーマットします。
SELECT TOP 3 JSON_ARRAY(E.%TABLENAME,E.Name,C.%TABLENAME,C.Name)
FROM Sample.Employee AS E,Sample.Company AS C
以下のダイナミック SQL の例では ODBC %SelectMode が設定されます。これは、JSON 配列の値を含むすべてのフィールドを表す方法を決定します。クエリは、%EXTERNAL フォーマット変換関数を適用することによって、特定の JSON 配列の要素に対応するこの選択モードをオーバーライドします。
SET myquery = 3
SET myquery(1) = "SELECT TOP 8 DOB,JSON_ARRAY(Name,DOB,FavoriteColors) AS ODBCMode, "
SET myquery(2) = "JSON_ARRAY(Name,DOB,%EXTERNAL(DOB),%EXTERNAL(FavoriteColors)) AS ExternalTrans "
SET myquery(3) = "FROM Sample.Person"
SET tStatement = ##class(%SQL.Statement).%New()
SET tStatement.%SelectMode=1
WRITE "SelectMode is ODBC",!
SET qStatus = tStatement.%Prepare(.myquery)
IF qStatus'=1 {WRITE "%Prepare failed:" DO $System.Status.DisplayError(qStatus) QUIT}
SET rset = tStatement.%Execute()
IF rset.%SQLCODE=0 { WRITE !,"Executed query",! }
ELSE { SET badSQL=##class(%Exception.SQL).%New(,rset.%SQLCODE,,rset.%Message)}
DO rset.%Display()
WRITE !,"End of data"
以下の例は、JSON_ARRAY を WHERE 節で使用して、OR 構文を使用しないで複数の列で Contains テストを実行します。
SELECT Name,Home_City,Home_State FROM Sample.Person
WHERE JSON_ARRAY(Name,Home_City,Home_State) [ 'X'
関連項目
-
SELECT 文
-
WHERE 節
-
JSON_OBJECT 関数
-
IS JSON の述語条件