この例では、FNC_GetInternalValueを使用して、JSONインスタンスの文字列表現を取得し、特定の名前と値ペアで検索します。
SQL定義:
REPLACE FUNCTION getJSONValue (a1 JSON(100)) RETURNS VARCHAR(100) NO SQL PARAMETER STYLE TD_GENERAL DETERMINISTIC LANGUAGE C EXTERNAL NAME 'CS!getJSONValue!getJSONValue.c!F!getJSONValue';
C関数定義、getJSONValue.c:
#define SQL_TEXT Latin_Text #include <sqltypes_td.h> #include <string.h> #include <stdio.h> #define buffer_size 64000 void getJSONValue (JSON_HANDLE *json_instance, VARCHAR_LATIN *result, char sqlstate[6]) { char *jsonBuf; char *searchString1, *searchString2; int maxLength = 0; charset_et charset = 0; int actualLength = 0; int strLength = 0; int numLobs = 0; /* Get the length of the JSON instance. */ FNC_GetJSONInfo((*json_instance), &maxLength, &charset, &numLobs); if (numLobs == 0) { jsonBuf = (char*)FNC_malloc(maxLength); FNC_GetInternalValue((*json_instance), (void*)jsonBuf, maxLength, &actualLength); searchString1 = strstr(jsonBuf, "name"); searchString2 = strchr(searchString1, ':'); searchString1 = strchr(searchString2, '}'); strLength = searchString1 - searchString2; strncpy(result,(searchString2+sizeof(char)), strLength-sizeof(char)); FNC_free(jsonBuf); } }
問合わせの例:
SELECT getJSONValue(NEW JSON('{"name":"Cameron"}'));
結果:
getJSONValue( NEW JSON('{"name":"Cameron"}', LATIN)) ---------------------------------------------------- "Cameron"