This example uses FNC_GetInternalValue to retrieve the string representation of a JSON instance, and then search it for a particular name-value pair.
SQL definition:
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 function definition, 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); } }
Example query:
SELECT getJSONValue(NEW JSON('{"name":"Cameron"}'));
Result:
getJSONValue( NEW JSON('{"name":"Cameron"}', LATIN)) ---------------------------------------------------- "Cameron"