SQLite

#define JSON_FALSE    2
#define JSON_INT      3
#define JSON_REAL     4
#define JSON_STRING   5
#define JSON_ARRAY    6
#define JSON_OBJECT   7




/*
** Names of the various JSON types:
*/
static const char * const jsonType[] = {
  "null", "true", "false", "integer", "real", "text", "array", "object"
};

/* Bit values for the JsonNode.jnFlag field
*/
#define JNODE_RAW     0x01         /* Content is raw, not JSON encoded */
#define JNODE_ESCAPE  0x02         /* Content is text with \ escapes */
#define JNODE_REMOVE  0x04         /* Do not output */
#define JNODE_REPLACE 0x08         /* Replace with JsonNode.iVal */
#define JNODE_APPEND  0x10         /* More ARRAY/OBJECT entries at u.iAppend */
#define JNODE_JSON    0x20         /* Treat REPLACE as JSON text */
#define JNODE_LABEL   0x40         /* Is a label of an object */


/* A single node of parsed JSON
*/
struct JsonNode {
  u8 eType;              /* One of the JSON_ type values */
  u8 jnFlags;            /* JNODE flags */

/*
** Append a function parameter value to the JSON string under 
** construction.
*/
static void jsonAppendValue(
  JsonString *p,                 /* Append to this JSON string */
  sqlite3_value *pValue,         /* Value to append */
  u8 textIsJson                  /* Try to treat text values as JSON */
){
  switch( sqlite3_value_type(pValue) ){
    case SQLITE_NULL: {
      jsonAppendRaw(p, "null", 4);
      break;
    }
    case SQLITE_INTEGER:
    case SQLITE_FLOAT: {
      const char *z = (const char*)sqlite3_value_text(pValue);
      u32 n = (u32)sqlite3_value_bytes(pValue);
      jsonAppendRaw(p, z, n);
      break;
    }
    case SQLITE_TEXT: {
      const char *z = (const char*)sqlite3_value_text(pValue);
      u32 n = (u32)sqlite3_value_bytes(pValue);
      if( textIsJson ){
        jsonAppendRaw(p, z, n);
      }else{
        jsonAppendString(p, z, n);
      }
      break;
    }
    default: {

      u32 j = 1;
      jsonAppendChar(pOut, '[');
      for(;;){
        while( j<=pNode->n ){
          if( pNode[j].jnFlags & (JNODE_REMOVE|JNODE_REPLACE) ){
            if( pNode[j].jnFlags & JNODE_REPLACE ){
              jsonAppendSeparator(pOut);
              jsonAppendValue(pOut, aReplace[pNode[j].iVal],
                              (pNode[j].jnFlags & JNODE_JSON)!=0);
            }
          }else{
            jsonAppendSeparator(pOut);
            jsonRenderNode(&pNode[j], pOut, aReplace);
          }
          j += jsonNodeSize(&pNode[j]);
        }

      for(;;){
        while( j<=pNode->n ){
          if( (pNode[j+1].jnFlags & JNODE_REMOVE)==0 ){
            jsonAppendSeparator(pOut);
            jsonRenderNode(&pNode[j], pOut, aReplace);
            jsonAppendChar(pOut, ':');
            if( pNode[j+1].jnFlags & JNODE_REPLACE ){
              jsonAppendValue(pOut, aReplace[pNode[j+1].iVal],
                              (pNode[j+1].jnFlags & JNODE_JSON)!=0);
            }else{
              jsonRenderNode(&pNode[j+1], pOut, aReplace);
            }
          }
          j += 1 + jsonNodeSize(&pNode[j+1]);
        }
        if( (pNode->jnFlags & JNODE_APPEND)==0 ) break;

  sqlite3_context *pCtx,      /* Return value for this function */
  sqlite3_value **aReplace    /* Array of replacement values */
){
  JsonString s;
  jsonInit(&s, pCtx);
  jsonRenderNode(pNode, &s, aReplace);
  jsonResult(&s);

}

/*
** Make the JsonNode the return value of the function.
*/
static void jsonReturn(
  JsonNode *pNode,            /* Node to return */

** Return NULL if not found or if there is an error.
**
** On an error, write an error message into pCtx and increment the
** pParse->nErr counter.
**
** If pApnd!=NULL then try to append missing nodes and set *pApnd = 1 if
** nodes are appended.
**
** If the path starts with $$ then set *pFlags to JNODE_REPLACE|JNODE_JSON
** as a single to the caller that the input text to be inserted should be 
** interpreted as JSON rather than as ordinary text.
*/
static JsonNode *jsonLookup(
  JsonParse *pParse,      /* The JSON to search */
  const char *zPath,      /* The path to search */
  int *pApnd,             /* Append nodes to complete path if not NULL */
  sqlite3_context *pCtx,  /* Report errors here, if not NULL */
  u8 *pFlags              /* Write JNODE_REPLACE or _REPLACE|_JSON here */
){
  const char *zErr = 0;
  JsonNode *pNode = 0;
  u8 fg = JNODE_REPLACE;

  if( zPath==0 ) return 0;
  if( zPath[0]!='$' ){
    zErr = zPath;
    goto lookup_err;
  }
  zPath++;
  if( zPath[0]=='$' ){
    if( pFlags==0 ){
      zErr = zPath;
      goto lookup_err;
    }
    zPath++;
    fg = JNODE_REPLACE|JNODE_JSON;
  }
  if( pFlags ) *pFlags = fg;
  pNode = jsonLookupStep(pParse, 0, zPath, pApnd, &zErr);
  return pNode;

lookup_err:
  pParse->nErr++;
  if( zErr!=0 && pCtx!=0 ){
    char *z = jsonPathSyntaxError(zErr);
    if( z ){
      sqlite3_result_error(pCtx, z, -1);
      sqlite3_free(z);
    }else{
      sqlite3_result_error_nomem(pCtx);
    }
  }
  if( pFlags ) *pFlags = fg;
  return 0;
}


/*
** Report the wrong number of arguments for json_insert(), json_replace()
** or json_set().

    jsonAppendRaw(&s, "\n", 1);
  }
  jsonParseReset(&x);
  jsonResult(&s);
}

/*
** The json_test1(JSON) function parses and rebuilds the JSON string.


*/
static void jsonTest1Func(
  sqlite3_context *ctx,
  int argc,
  sqlite3_value **argv
){
  JsonParse x;  /* The parse */
  if( jsonParse(&x, ctx, (const char*)sqlite3_value_text(argv[0])) ) return;
  jsonReturnJson(x.aNode, ctx, 0);
  jsonParseReset(&x);
}

/*
** The json_nodecount(JSON) function returns the number of nodes in the
** input JSON string.
*/
static void jsonNodeCountFunc(
  sqlite3_context *ctx,
  int argc,
  sqlite3_value **argv
){
  JsonParse x;  /* The parse */
  if( jsonParse(&x, ctx, (const char*)sqlite3_value_text(argv[0])) ) return;
  sqlite3_result_int64(ctx, (sqlite3_int64)x.nNode);
  jsonParseReset(&x);
}
#endif /* SQLITE_DEBUG */

/****************************************************************************
** SQL function implementations
****************************************************************************/

  int i;
  JsonString jx;

  jsonInit(&jx, ctx);
  jsonAppendChar(&jx, '[');
  for(i=0; i<argc; i++){
    jsonAppendSeparator(&jx);
    jsonAppendValue(&jx, argv[i], 0);
  }
  jsonAppendChar(&jx, ']');
  jsonResult(&jx);

}


/*
** json_array_length(JSON)
** json_array_length(JSON, PATH)
**

  u32 i;

  if( jsonParse(&x, ctx, (const char*)sqlite3_value_text(argv[0])) ) return;
  if( x.nNode ){
    JsonNode *pNode;
    if( argc==2 ){
      const char *zPath = (const char*)sqlite3_value_text(argv[1]);
      pNode = jsonLookup(&x, zPath, 0, ctx, 0);
    }else{
      pNode = x.aNode;
    }
    if( pNode==0 ){
      x.nErr = 1;
    }else if( pNode->eType==JSON_ARRAY ){
      assert( (pNode->jnFlags & JNODE_APPEND)==0 );

  if( argc<2 ) return;
  if( jsonParse(&x, ctx, (const char*)sqlite3_value_text(argv[0])) ) return;
  jsonInit(&jx, ctx);
  jsonAppendChar(&jx, '[');
  for(i=1; i<argc; i++){
    zPath = (const char*)sqlite3_value_text(argv[i]);
    pNode = jsonLookup(&x, zPath, 0, ctx, 0);
    if( x.nErr ) break;
    if( argc>2 ){
      jsonAppendSeparator(&jx);
      if( pNode ){
        jsonRenderNode(pNode, &jx, 0);
      }else{
        jsonAppendRaw(&jx, "null", 4);
      }
    }else if( pNode ){
      jsonReturn(pNode, ctx, 0);
    }
  }
  if( argc>2 && i==argc ){
    jsonAppendChar(&jx, ']');
    jsonResult(&jx);

  }
  jsonReset(&jx);
  jsonParseReset(&x);
}

/*
** Implementation of the json_object(NAME,VALUE,...) function.  Return a JSON

      return;
    }
    jsonAppendSeparator(&jx);
    z = (const char*)sqlite3_value_text(argv[i]);
    n = (u32)sqlite3_value_bytes(argv[i]);
    jsonAppendString(&jx, z, n);
    jsonAppendChar(&jx, ':');
    jsonAppendValue(&jx, argv[i+1], 0);
  }
  jsonAppendChar(&jx, '}');
  jsonResult(&jx);

}


/*
** json_remove(JSON, PATH, ...)
**
** Remove the named elements from JSON and return the result.  malformed

  if( argc<1 ) return;
  if( jsonParse(&x, ctx, (const char*)sqlite3_value_text(argv[0])) ) return;
  if( x.nNode ){
    for(i=1; i<(u32)argc; i++){
      zPath = (const char*)sqlite3_value_text(argv[i]);
      if( zPath==0 ) goto remove_done;
      pNode = jsonLookup(&x, zPath, 0, ctx, 0);
      if( x.nErr ) goto remove_done;
      if( pNode ) pNode->jnFlags |= JNODE_REMOVE;
    }
    if( (x.aNode[0].jnFlags & JNODE_REMOVE)==0 ){
      jsonReturnJson(x.aNode, ctx, 0);
    }
  }

  if( (argc&1)==0 ) {
    jsonWrongNumArgs(ctx, "replace");
    return;
  }
  if( jsonParse(&x, ctx, (const char*)sqlite3_value_text(argv[0])) ) return;
  if( x.nNode ){
    for(i=1; i<(u32)argc; i+=2){
      u8 jnFlags = JNODE_REPLACE;
      zPath = (const char*)sqlite3_value_text(argv[i]);
      pNode = jsonLookup(&x, zPath, 0, ctx, &jnFlags);
      if( x.nErr ) goto replace_err;
      if( pNode ){
        pNode->jnFlags &= ~JNODE_JSON;
        pNode->jnFlags |= jnFlags;
        pNode->iVal = i+1;
      }
    }
    if( x.aNode[0].jnFlags & JNODE_REPLACE ){
      sqlite3_result_value(ctx, argv[x.aNode[0].iVal]);
    }else{
      jsonReturnJson(x.aNode, ctx, argv);

  if( (argc&1)==0 ) {
    jsonWrongNumArgs(ctx, bIsSet ? "set" : "insert");
    return;
  }
  if( jsonParse(&x, ctx, (const char*)sqlite3_value_text(argv[0])) ) return;
  if( x.nNode ){
    for(i=1; i<(u32)argc; i+=2){
      u8 jnFlags = JNODE_REPLACE;
      zPath = (const char*)sqlite3_value_text(argv[i]);
      bApnd = 0;
      pNode = jsonLookup(&x, zPath, &bApnd, ctx, &jnFlags);
      if( x.oom ){
        sqlite3_result_error_nomem(ctx);
        goto jsonSetDone;
      }else if( x.nErr ){
        goto jsonSetDone;
      }else if( pNode && (bApnd || bIsSet) ){
        pNode->jnFlags &= ~JNODE_JSON;
        pNode->jnFlags |= jnFlags;
        pNode->iVal = i+1;
      }
    }
    if( x.aNode[0].jnFlags & JNODE_REPLACE ){
      sqlite3_result_value(ctx, argv[x.aNode[0].iVal]);
    }else{
      jsonReturnJson(x.aNode, ctx, argv);

  const char *zPath;

  if( jsonParse(&x, ctx, (const char*)sqlite3_value_text(argv[0])) ) return;
  if( x.nNode ){
    JsonNode *pNode;
    if( argc==2 ){
      zPath = (const char*)sqlite3_value_text(argv[1]);
      pNode = jsonLookup(&x, zPath, 0, ctx, 0);
    }else{
      pNode = x.aNode;
    }
    if( pNode ){
      sqlite3_result_text(ctx, jsonType[pNode->eType], -1, SQLITE_STATIC);
    }
  }

  unsigned int i;
  static const struct {
     const char *zName;
     int nArg;
     int flag;
     void (*xFunc)(sqlite3_context*,int,sqlite3_value**);
  } aFunc[] = {

    { "json_array",          -1, 0,   jsonArrayFunc         },
    { "json_array_length",    1, 0,   jsonArrayLengthFunc   },
    { "json_array_length",    2, 0,   jsonArrayLengthFunc   },
    { "json_extract",        -1, 0,   jsonExtractFunc       },
    { "json_insert",         -1, 0,   jsonSetFunc           },
    { "json_object",         -1, 0,   jsonObjectFunc        },
    { "json_remove",         -1, 0,   jsonRemoveFunc        },
    { "json_replace",        -1, 0,   jsonReplaceFunc       },
    { "json_set",            -1, 1,   jsonSetFunc           },
    { "json_type",            1, 0,   jsonTypeFunc          },
    { "json_type",            2, 0,   jsonTypeFunc          },
    { "json_valid",           1, 0,   jsonValidFunc         },

#if SQLITE_DEBUG
    /* DEBUG and TESTING functions */
    { "json_parse",           1, 0,   jsonParseFunc         },
    { "json_test1",           1, 0,   jsonTest1Func         },
    { "json_nodecount",       1, 0,   jsonNodeCountFunc     },
#endif
  };
#ifndef SQLITE_OMIT_VIRTUALTABLE
  static const struct {
     const char *zName;
     sqlite3_module *pModule;
  } aMod[] = {

# SQLite library.
#

set testdir [file dirname $argv0]
source $testdir/tester.tcl

load_static_extension db json
do_execsql_test json1-1.1 {
  SELECT json_array(1,2.5,null,'hello');
} {[1,2.5,null,"hello"]}












do_execsql_test json1-1.2 {
  SELECT hex(json_array('String "\ Test'));
} {5B22537472696E67205C225C5C2054657374225D}
do_catchsql_test json1-1.3 {
  SELECT json_array(1,2,x'abcd',3);
} {1 {JSON cannot hold BLOB values}}
do_execsql_test json1-1.4 {

  SELECT json_object('a',1,'b',x'abcd');
} {1 {JSON cannot hold BLOB values}}

do_execsql_test json1-3.1 {
  SELECT json_replace('{"a":1,"b":2}','$.a','[3,4,5]');
} {{{"a":"[3,4,5]","b":2}}}
do_execsql_test json1-3.2 {
  SELECT json_replace('{"a":1,"b":2}','$$.a','[3,4,5]');
} {{{"a":[3,4,5],"b":2}}}
do_execsql_test json1-3.3 {
  SELECT json_type(json_set('{"a":1,"b":2}','$.b','{"x":3,"y":4}'),'$.b');
} {text}
do_execsql_test json1-3.4 {
  SELECT json_type(json_set('{"a":1,"b":2}','$$.b','{"x":3,"y":4}'),'$.b');
} {object}

# Per rfc7159, any JSON value is allowed at the top level, and whitespace
# is permitting before and/or after that value.
#
do_execsql_test json1-4.1 {
  CREATE TABLE j1(x);