Hi. I store large minified JSON values in SQLite (no indenting).
It's compact and machine-processed using SQLite and outside too.

But sometimes I want to explicitly pretty-print an indented and more readable version,
to read / examine the JSON as a human, within the SQLite CLI or non-JSON aware SQLite GUIs.

I've looked in https://www.sqlite.org/json1.html and see nothing to maximize (indent) JSON.
Only the reverse, to minify it via https://www.sqlite.org/json1.html#jmini.

So am I missing it? Or it's just not there?
And if not there, could it be added eventually?

There are ways to format JSON outside SQLite of course (e.g. jq).
But I'd like one that's within SQLite / JSON1 basically.

Yes, this is about presentation kinda. If Keith was still with us, he'd say so I bet.
But given the special place JSON has in SQLite, this seems like missing functionality to me¹, no?

Thanks, --DD

---

1. ^{^} 1 unless again I've missed it in the doc, of course

Presumably you could do something with one of the JSON table valued functions if you were enthused into rolling something yourself.

Search for all the atoms.
Indent based on depth within the tree.
Profit?

This would be very useful in the SQLite shell indeed. In the library itself there's probably not much need for it; seems like something you want to handle at the application layer since it's indeed a presentation thing.

Maybe an 'indent' option for the JSON output mode that specifies the indent size. The top level output would then be pretty printed using that and any JSON values that are detected using json_valid would be as well?

The Shell's .mode json has nothing to do with what I'm asking.
I don't want to style the whole rows / result-sets as JSON.
I want to style individual values, possibly conditionally.

What I'm asking about is more akin to hex() and printf() basically.

And it is trivially done with a few surgical changes in the json.c file,
i.e. directly in the library, and its JSON subset, probably adding a member
or two in JsonString, and changing jsonRenderNode() and jsonAppendSeparator().

Then deciding how to expose it, via json_quote() perhaps,
or a new json_ident(), json_pretty(), or json_print() function.

I think I could even do it myself, but 1) SQLite is not open to contributions,
and 2) DRH can it better and faster. Seems like such a no-brainer to me that
indenting JSON is generally useful, that I was surprised to discover it's not
there when I needed it.

I often wonder why things that seem obvious to me, are somehow not to others :)

I think I could even do it myself, but 1) SQLite is not open to contributions, and 2) DRH can it better and faster.

FWIW: oftentimes, receiving a proof-of-concept implementation will provoke a developer into taking on a task they might not have otherwise had on their TODO list. (Hint, hint!)

Seems like such a no-brainer to me that indenting JSON is generally useful, that I was surprised to discover it's not there when I needed it.

You'd be hard-pressed to find many presentation-related functions within the library. printf() and strftime() come to mind, but no others immediately do, and certainly none as elaborate as JSON indentation.

i can attest from experience that there are nuances to JSON formatting which make it more complicated than it might initially sound, e.g. whether or not to add spaces after a ":", or between comma-separated array entries, are matters of taste, and as soon as someone posts code which does it one way but not the other, someone is going to cry out, "it's useless to me like this!"

That's not to say that this wouldn't be an interesting addition, just to say that there's more to it than just indentation. (Speaking of: some folks will demand spaces and some will demand hard tabs. There is no one-size-fits-all JSON pretty-printing implementation.)

Gross, yes.

Slow, maybe. If it's a human consumable volume of output, the speed is probably irrelevant. You aren't going to process gigabytes of JSON for someone to read...

I already thought about spaces vs tabs, can do done. Any other chars doesn't make sense to me.

Regarding styles, I can think of 3 main ones below (2-space indent).

Personally, I don't care. I just want to read otherwise unreadable JSON,
similar to how I'd explicitly add hex(blob_value) in some SELECT SQL.

For hex(), I don't choose lower vs upper case for example. Same thing here,
just one simple-to-implement indent-style that doesn't try to be fancy,
like maximizing values fitting into an 80-character line for example,
always 1-value-per-line (like shown below).

And Stephan, I doubt a POC of mine will move the needle for Richard, TBH.
Either he agrees this is generally useful enough (my hope), or he doesn't.

This reminds me a bit of the thousand-separator arguments I made years ago.
Got tons of pushback, lite this, locale that, presentation blah, etc...
And in 15 lines of code or so, Richard settled the cacophony and just did it.

I suspect it's the same thing here. Either he gets it, and the code analysis
I made above is mostly right, and as cheap as I think it is, so he just does it.
Or this will go down as yet another one of my unanswered posts and/or feature requests.

JSON1 has minifying already. Makes sense to have its opposite as well,
for symmetry, i.e. pretty-printing via indenting IMHO. FWIW.
It will definitely be useful to me! And many others I suspect.

{
  "foo": [
    "bar",
    true,
    1
  ]
}

{
  "foo":
  [
    bar",
    true,
    1
  ]
}

{ "foo":
  [ "bar"
  , true
  , 1
  ]
}

You don't have to leave the SQLite CLI to pipe output to jq :

CREATE TABLE T2 (
  zjson TEXT
);
INSERT INTO T2 VALUES ('[ {"product":"chips","price":2.00,"qty":4},{"product":"juice","price":2.99,"qty":1} ]');
.echo off
.headers off
.mode list
SELECT zjson FROM T2;
.output '| /usr/bin/jq [.]'
SELECT zjson FROM T2;
.output

I am not sure if this is what you are looking for ?

I often wonder why things that seem obvious to me, are somehow not to others :)

FWIW, it seems pretty obvious to me too. Our application uses SQLite extensively for key/value style storage where the values are often sizeable chunks of JSON. When inspecting these databases manually the minified JSON can be annoying.

Having a built-in pretty printing function would be ideal. There is precedent for this kind of thing in other SQL implementations (PostgreSQL has jsonb_pretty for instance). The .json output mode suggestion was just as a possible alternative that might have a higher chance of being accepted because it's not a library change.

a possible alternative that might have a higher chance of being accepted

Doing the pretty-printing on the outside of the lib, in the shell,
means having to re-implement a full JSON parser to do the pretty-printing.
Thus resulting in a MUCH larger change. Thus lowering its chances instead.

While doing it inside allows to use the private-impl parser.
And as I already wrote, requires only minimal changes to existing functions.

I probably didn't word my followup carefully enough. I wasn't trying to argue that a solution in the shell would be better, nor that a solution with a JSON1 function would be worse. Just trying to clarify what I was thinking at the time of writing.

My assumption was that backwards compatibility requirements for library functions are much stronger than for shell functionality so it's easier to try stuff out in the shell without committing to a public function signature. That's just my assumption; could be irrelevant. I don't have a stake in this game, so I'll stay out of the discussion.

A json_pretty() function is now on trunk. Preliminary documentation at https://sqlite.org/draft/json1.html#jpretty. Available for testing on fiddle.

Thank you. Thank you. Thank you.

Here's the change for those interested.

I see you decided to add a new function instead of shoehorning pretty-printing to the existing Render code, resulting in a larger change that I'd have dared.
Last night, I actually started on it... Prepared a workspace with our amalgamation. Added a couple tests on json() and json_quote() to catch regressions.
Stepping through the code in the debugger to get a feel for where to splice minimal changes. Glad you beat me to it. Karma perhaps even. Thanks again. --DD