SQLite Forum

Seems interesting to me. I'd use the arrow operators, though the n-functions wouldn't be useful for my (currently existing) code.

On the topic of JSON enhancements, I still think there's a place for a json_contains() function that checks if a JSON array or object has a specified value. This was useful for me. On that note, I ended up making it myself in my extension, so if someone happens to want it, you can use my amateur-ish code (though it does #include json1.c which bloats the file a bit):

```
static void json_contains_func(
  sqlite3_context *ctx,
  int argc,
  sqlite3_value **argv
){
  /*
  Check JSON for a specified value

  Checks a JSON string for a specific value. Works on both JSON objects and
  JSON arrays. JSON object keys are assumed to be strings. Limitations in
  SQLite mean true and false values cannot be checked for in arrays.

  Args:
    JSON String: The JSON string
    Any Value: The value to check for
    Text Path: An optional path to use when checking for the value

  Returns:
    Integer: 1 or 0 if the value is found or not
  */
  JsonParse *p;
  int b = 0;
  u32 i;
  JsonNode *pNode;
  char* zPath;

  p = jsonParseCached(ctx, argv, ctx);
  if( p==0 ) return;
  assert( p->nNode );
  if( argc==3 ){
    zPath = (char*)sqlite3_value_text(argv[2]);
    pNode = jsonLookup(p, zPath, 0, ctx);
  }else{
    zPath = "$";
    pNode = p->aNode;
  }

  if( pNode==0 ){
    return;
  }

  if( pNode->eType==JSON_ARRAY ){
    assert( (pNode->jnFlags & JNODE_APPEND)==0 );
    JsonNode *iNode;
    int arg_type;

    arg_type = sqlite3_value_type(argv[1]);

    for(i=1; i<=pNode->n; i++){
      iNode = &pNode[i];
      switch (iNode->eType) {
        case JSON_TRUE:
        case JSON_FALSE:
        case JSON_ARRAY:
        case JSON_OBJECT:
          break;
        case JSON_NULL:
          if ( arg_type==SQLITE_NULL ) {
            b = 1;
            goto value_found;
          }
          break;
        case JSON_STRING:
          if (arg_type != SQLITE_TEXT) break;
          char* value;
          value = sqlite3_malloc((iNode->n)-1);
          if ( value==0 ){
            sqlite3_result_error_nomem(ctx);
            return;
          }
          strncpy(value, (char*)(iNode->u.zJContent+1), (iNode->n)-1);
          value[(iNode->n)-2] = '\0';
          if ( !strcmp((char*)sqlite3_value_text(argv[1]), value) ) {
            b = 1;
            sqlite3_free(value);
            goto value_found;
          }
          sqlite3_free(value);
          break;
        case JSON_INT:
          if (arg_type != SQLITE_INTEGER) break;
          sqlite3_int64 i = 0;
          const char *z = iNode->u.zJContent;
          if( z[0]=='-' ){ z++; }
          while( z[0]>='0' && z[0]<='9' ){
            unsigned v = *(z++) - '0';
            if( i>=LARGEST_INT64/10 ){
              if( i>LARGEST_INT64/10 ) goto end;
              if( z[0]>='0' && z[0]<='9' ) goto end;
              if( v==9 ) goto end;
              if( v==8 ){
                if( pNode->u.zJContent[0]=='-' ){
                  goto int_done;
                }else{
                  goto end;
                }
              }
            }
            i = i*10 + v;
          }
          if( iNode->u.zJContent[0]=='-' ){ i = -i; }
          int_done:
          if ( sqlite3_value_int64(argv[1])==i ) {
            b = 1;
            goto value_found;
          }
          end:
          break;
        case JSON_REAL:
          if (arg_type != SQLITE_FLOAT) break;
          double d = strtod(iNode->u.zJContent, 0);
          if ( (double)sqlite3_value_double(argv[1])==d ) {
            b = 1;
            goto value_found;
          }
      }
    }
  } else if ( pNode->eType==JSON_OBJECT ){
    JsonNode *kp;
    char *kPath;
    char *arg_str;
    char *arg;

    arg = (char*)sqlite3_value_text(argv[1]);

    if ( strlen(arg)>2 && arg[0]=='"' && arg[strlen(arg)-1]=='"' ) {
      arg_str = sqlite3_malloc(strlen(arg)-1);
      if ( arg_str==0 ){
        sqlite3_result_error_nomem(ctx);
        return;
      }
      strncpy(arg_str, arg+1, strlen(arg)-2);
    } else {
      arg_str = sqlite3_malloc(strlen(arg)+1);
      if ( arg_str==0 ){
        sqlite3_result_error_nomem(ctx);
        return;
      }
      strcpy(arg_str, arg);
    }

    kPath = sqlite3_malloc(strlen(zPath)+1+strlen(arg_str)+1);
    if ( kPath==0 ){
      sqlite3_result_error_nomem(ctx);
      return;
    }
    strcpy(kPath, zPath);
    strcat(kPath, ".");
    strcat(kPath, arg_str);

    kp = jsonLookup(p, kPath, NULL, ctx);
    if ( kp != 0 ) {
      b = 1;
    }
    sqlite3_free(kPath);
    sqlite3_free(arg_str);
  }
  value_found:
  sqlite3_result_int(ctx, b);
}```