A MySQL UDF to flatten JSON using phpembed-cmake

presto.cpp

#include "presto.h"
#include "myphp.h"
#include <iostream>
#include <string>

/* This function hooks PHP output to the error log*/ 
void php_log_output(const char *str){
 std::cerr << str;
}

/* Check that there is one string arg*/
my_bool flatten_json_init(UDF_INIT *initid, UDF_ARGS *args, char* message) {
 if(args->arg_count != 1 || args->arg_type[0] != STRING_RESULT) {
 strcpy(message,"Takes one string arg: json_doc");
 return 1;
 }

 return 0;
}

/* do the actual work of the function*/
char* flatten_json(UDF_INIT *initid, UDF_ARGS *args,char *result, long long *length, char *is_null, char *error) {

  if(args->args[0] == NULL) {
    *is_null = 1;
    return 0;
  }

  /* This is a PHP script to flatten the JSON*/
  const char script[] =
R"(
function do_flatten($val, $key) {
  global $out;
  if(is_numeric($val)) {
    $out .= "$key=$val\n";
  } else {
    // output whole string
    $out.= "$key=$val\n";

    // output tokenized substrings
    $tok = strtok($val, "\n\t \r");
    $cnt = 0;
    $out2 = "";
    while($tok !== false) {
      ++$cnt;
      $out2 .= "$key=$tok\n";
      $tok = strtok("\n\t \r.");
    }
    if($cnt>1) $out .= $out2;
  }

} 

function php_json_flatten($json) {
  global $out;
  array_walk_recursive(json_decode($json,true), 'do_flatten');
  return $out;
} )";
/*END PHP*/

  // Start the PHP interpreter
  php p(true);
  p.set_message_function(php_log_output);
  p.set_output_function(php_log_output);
  p.set_error_function(php_log_output);

  // Parse the PHP script
  p.eval_string(script);

  // flatten JSON with PHP
  unsigned int len; // how long is return string 
  char* flat = p.call_c_string_ex("php_json_flatten",&len, "S",args->args[0], args->lengths[0]);

  initid->ptr = (char*)malloc(len+1); 
  strncpy(initid->ptr, flat, len);
  *(initid->ptr+len)='\0';
  *length=len;

  return initid->ptr;

}

void flatten_json_deinit(UDF_INIT *initid) {
  delete[] initid->ptr;
}