Example: Getting the Value of a JSON Type - Teradata Vantage - Analytics Database

This example uses FNC_GetInternalValue to retrieve the string representation of a JSON instance, and search the string 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"