SQLite

          "    SELECT max(iter), cx, cy FROM m GROUP BY cx, cy\n",
          "  ),\n",
          "  a(t) AS (\n",
          "    SELECT group_concat( substr(' .+*#', 1+min(iter/7,4), 1), '') \n",
          "    FROM m2 GROUP BY cy\n",
          "  )\n",
          "SELECT group_concat(rtrim(t),x'0a') as Mandelbrot FROM a;\n",
        ]},
        {name: "JSON pretty-print",
         sql: "select json_pretty(json_object('ex',json('[52,3.14159]')))"
        },
        {name: "JSON pretty-print (with tabs)",
         sql: "select json_pretty(json_object('ex',json('[52,3.14159]')),char(0x09))"
        }
      ];
      const newOpt = function(lbl,val){
        const o = document.createElement('option');
        if(Array.isArray(val)) val = val.join('');
        o.value = val;
        if(!val) o.setAttribute('disabled',true);
        o.appendChild(document.createTextNode(lbl));

    jsonStringExpandAndAppend(p,zIn,N);
  }else{
    memcpy(p->zBuf+p->nUsed, zIn, N);
    p->nUsed += N;
  }
}


/* Append formatted text (not to exceed N bytes) to the JsonString.
*/
static void jsonPrintf(int N, JsonString *p, const char *zFormat, ...){
  va_list ap;
  if( (p->nUsed + N >= p->nAlloc) && jsonStringGrow(p, N) ) return;
  va_start(ap, zFormat);
  sqlite3_vsnprintf(N, p->zBuf+p->nUsed, zFormat, ap);

      malformed_jsonb:
      pOut->eErr |= JSTRING_MALFORMED;
      break;
    }
  }
  return i+n+sz;
}

/* Context for recursion of json_pretty()
*/
typedef struct JsonPretty JsonPretty;
struct JsonPretty {
  JsonParse *pParse;        /* The BLOB being rendered */
  JsonString *pOut;         /* Generate pretty output into this string */
  const char *zIndent;      /* Use this text for indentation */
  u32 szIndent;             /* Bytes in zIndent[] */
  u32 nIndent;              /* Current level of indentation */
};

/* Append indentation to the pretty JSON under construction */
static void jsonPrettyIndent(JsonPretty *pPretty){
  u32 jj;
  for(jj=0; jj<pPretty->nIndent; jj++){
    jsonAppendRaw(pPretty->pOut, pPretty->zIndent, pPretty->szIndent);
  }
}

/*
** Translate the binary JSONB representation of JSON beginning at
** pParse->aBlob[i] into a JSON text string.  Append the JSON
** text onto the end of pOut.  Return the index in pParse->aBlob[]
** of the first byte past the end of the element that is translated.
**
** This is a variant of jsonTranslateBlobToText() that "pretty-prints"
** the output.  Extra whitespace is inserted to make the JSON easier
** for humans to read.
**
** If an error is detected in the BLOB input, the pOut->eErr flag
** might get set to JSTRING_MALFORMED.  But not all BLOB input errors
** are detected.  So a malformed JSONB input might either result
** in an error, or in incorrect JSON.
**
** The pOut->eErr JSTRING_OOM flag is set on a OOM.
*/
static u32 jsonTranslateBlobToPrettyText(
  JsonPretty *pPretty,       /* Pretty-printing context */
  u32 i                      /* Start rendering at this index */
){
  u32 sz, n, j, iEnd;
  const JsonParse *pParse = pPretty->pParse;
  JsonString *pOut = pPretty->pOut;
  n = jsonbPayloadSize(pParse, i, &sz);
  if( n==0 ){
    pOut->eErr |= JSTRING_MALFORMED;
    return pParse->nBlob+1;
  }
  switch( pParse->aBlob[i] & 0x0f ){
    case JSONB_ARRAY: {
      j = i+n;
      iEnd = j+sz;
      jsonAppendChar(pOut, '[');
      if( j<iEnd ){
        jsonAppendChar(pOut, '\n');
        pPretty->nIndent++;
        while( pOut->eErr==0 ){
          jsonPrettyIndent(pPretty);
          j = jsonTranslateBlobToPrettyText(pPretty, j);
          if( j>=iEnd ) break;
          jsonAppendRawNZ(pOut, ",\n", 2);
        }
        jsonAppendChar(pOut, '\n');
        pPretty->nIndent--;
        jsonPrettyIndent(pPretty);
      }
      jsonAppendChar(pOut, ']');
      i = iEnd;
      break;
    }
    case JSONB_OBJECT: {
      j = i+n;
      iEnd = j+sz;
      jsonAppendChar(pOut, '{');
      if( j<iEnd ){
        jsonAppendChar(pOut, '\n');
        pPretty->nIndent++;
        while( pOut->eErr==0 ){
          jsonPrettyIndent(pPretty);
          j = jsonTranslateBlobToText(pParse, j, pOut);
          if( j>iEnd ){
            pOut->eErr |= JSTRING_MALFORMED;
            break;
          }
          jsonAppendRawNZ(pOut, ": ", 2);
          j = jsonTranslateBlobToPrettyText(pPretty, j);
          if( j>=iEnd ) break;
          jsonAppendRawNZ(pOut, ",\n", 2);
        }
        jsonAppendChar(pOut, '\n');
        pPretty->nIndent--;
        jsonPrettyIndent(pPretty);
      }
      jsonAppendChar(pOut, '}');
      i = iEnd;
      break;
    }
    default: {
      i = jsonTranslateBlobToText(pParse, i, pOut);
      break;
    }
  }
  return i;
}


/* Return true if the input pJson
**
** For performance reasons, this routine does not do a detailed check of the
** input BLOB to ensure that it is well-formed.  Hence, false positives are
** possible.  False negatives should never occur, however.
*/

  }else{
    i = 0;
  }
  sqlite3_result_text(ctx, jsonbType[p->aBlob[i]&0x0f], -1, SQLITE_STATIC);
json_type_done:
  jsonParseFree(p);
}

/*
** json_pretty(JSON)
** json_pretty(JSON, INDENT)
**
** Return text that is a pretty-printed rendering of the input JSON.
** If the argument is not valid JSON, return NULL.
**
** The INDENT argument is text that is used for indentation.  If omitted,
** it defaults to four spaces (the same as PostgreSQL).
*/
static void jsonPrettyFunc(
  sqlite3_context *ctx,
  int argc,
  sqlite3_value **argv
){
  JsonString s;          /* The output string */
  JsonPretty x;          /* Pretty printing context */

  memset(&x, 0, sizeof(x));
  x.pParse = jsonParseFuncArg(ctx, argv[0], 0);
  if( x.pParse==0 ) return;
  x.pOut = &s;
  jsonStringInit(&s, ctx);
  if( argc==1 || (x.zIndent = (const char*)sqlite3_value_text(argv[1]))==0 ){
    x.zIndent = "    ";
    x.szIndent = 4;
  }else{
    x.szIndent = (u32)strlen(x.zIndent);
  }
  jsonTranslateBlobToPrettyText(&x, 0);
  jsonReturnString(&s, 0, 0);
  jsonParseFree(x.pParse);
}

/*
** json_valid(JSON)
** json_valid(JSON, FLAGS)
**
** Check the JSON argument to see if it is well-formed.  The FLAGS argument
** encodes the various constraints on what is meant by "well-formed":

    JFUNCTION(->>,                2,1,0, 0,0,JSON_SQL,   jsonExtractFunc),
    JFUNCTION(json_insert,       -1,1,1, 1,0,0,          jsonSetFunc),
    JFUNCTION(jsonb_insert,      -1,1,0, 1,1,0,          jsonSetFunc),
    JFUNCTION(json_object,       -1,0,1, 1,0,0,          jsonObjectFunc),
    JFUNCTION(jsonb_object,      -1,0,1, 1,1,0,          jsonObjectFunc),
    JFUNCTION(json_patch,         2,1,1, 0,0,0,          jsonPatchFunc),
    JFUNCTION(jsonb_patch,        2,1,0, 0,1,0,          jsonPatchFunc),
    JFUNCTION(json_pretty,        1,1,0, 0,0,0,          jsonPrettyFunc),
    JFUNCTION(json_pretty,        2,1,0, 0,0,0,          jsonPrettyFunc),
    JFUNCTION(json_quote,         1,0,1, 1,0,0,          jsonQuoteFunc),
    JFUNCTION(json_remove,       -1,1,1, 0,0,0,          jsonRemoveFunc),
    JFUNCTION(jsonb_remove,      -1,1,0, 0,1,0,          jsonRemoveFunc),
    JFUNCTION(json_replace,      -1,1,1, 1,0,0,          jsonReplaceFunc),
    JFUNCTION(jsonb_replace,     -1,1,0, 1,1,0,          jsonReplaceFunc),
    JFUNCTION(json_set,          -1,1,1, 1,0,JSON_ISSET, jsonSetFunc),
    JFUNCTION(jsonb_set,         -1,1,0, 1,1,JSON_ISSET, jsonSetFunc),

  } 0
  do_execsql_test $ii.7 {
    UPDATE t1 SET p=json_patch(j0,j5);
    SELECT count(*)
      FROM t1, kv
     WHERE p->>key IS NOT val
  } 0
  do_execsql_test $ii.8 {
    SELECT j0 FROM t1 WHERE json(j0)!=json(json_pretty(j0));
  } {}
  do_execsql_test $ii.9 {
    SELECT j5 FROM t1 WHERE json(j5)!=json(json_pretty(j5));
  } {}
}


finish_test

# 2024-03-06
#
# The author disclaims copyright to this source code.  In place of
# a legal notice, here is a blessing:
#
#    May you do good and not evil.
#    May you find forgiveness for yourself and forgive others.
#    May you share freely, never taking more than you give.
#
#***********************************************************************
# Invariant tests for JSON built around the randomjson extension
#

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

# These tests require virtual table "json_tree" to run.
ifcapable !vtab { finish_test ; return }

load_static_extension db randomjson
db eval {
  CREATE TEMP TABLE t1(j0,j5);
  WITH RECURSIVE c(n) AS (VALUES(0) UNION ALL SELECT n+1 FROM c WHERE n<9)
  INSERT INTO t1 SELECT random_json(n), random_json5(n) FROM c;
}

do_execsql_test 1.1 {
  SELECT count(*) FROM t1 WHERE json(j0)==json(json_pretty(j0,NULL));
} 10
do_execsql_test 1.2 {
  SELECT count(*) FROM t1 WHERE json(j0)==json(json_pretty(j0,NULL));
} 10
do_execsql_test 1.3 {
  SELECT count(*) FROM t1 WHERE json(j0)==json(json_pretty(j0,''));
} 10
do_execsql_test 1.4 {
  SELECT count(*) FROM t1 WHERE json(j0)==json(json_pretty(j0,char(9)));
} 10
do_execsql_test 1.5 {
  SELECT count(*) FROM t1 WHERE json(j0)==json(json_pretty(j0,'/*hello*/'));
} 10


finish_test