Thread: mogrify and indent features for jsonb

Attached is a patch to provide a number of very useful facilities to
jsonb that people have asked for. These are based on work by Dmitry
Dolgov in his jsonbx extension, but I take responsibility for any bugs.

The facilities are:

new operations:

concatenation:    jsonb || jsonb -> jsonb
deletion:         jsonb - text -> jsonb
deletion:         jsonb - int -> text

new functions:

produce indented text: jsonb_indent(jsonb) -> text
change an element at a path:  jsonb_replace(jsonb, text[], jsonb) -> jsonb.


It would be relatively trivial to add:

delete an element at a path: jsonb_delete(jsonb, text[]) -> json

and I think we should do that for the sake of completeness.

The docs might need a little extra work, and the indent code definitely
needs work, which I hope to complete in the next day or two, but I
wanted to put a stake in the ground.


cheers

andrew

For jsonb_indent, how about having it match up closer to the
JavaScript JSON.stringify(value, replacer, space)[1]? That way a user
can specify the indentation level and optionally filter the fields
they'd like to output.

In JS, the "replacer" parameter can be either a JS function or an
array of property names. I don't think the former is really possible
(in a SQL callable function) but the latter would be a text[]. The
"space" parameter can be either a string (used directly) or a number
(corresponding number of spaces).

The PG function signatures would be something like:

CREATE OR REPLACE FUNCTION jsonb_stringify(obj jsonb, replacer text[],
space text)
CREATE OR REPLACE FUNCTION jsonb_stringify(obj jsonb, replacer text[],
space int)

For convenience we could also include overloads with replacer removed
(since most people probably want the entire object):

CREATE OR REPLACE FUNCTION jsonb_stringify(obj jsonb, space text)
CREATE OR REPLACE FUNCTION jsonb_stringify(obj jsonb, space int)

Having json_stringify versions of these would be useful as well.

Regards,
-- Sehrope Sarkuni

[1]: https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Global_Objects/JSON/stringify

On 02/15/2015 11:47 AM, Sehrope Sarkuni wrote:
> For jsonb_indent, how about having it match up closer to the
> JavaScript JSON.stringify(value, replacer, space)[1]? That way a user
> can specify the indentation level and optionally filter the fields
> they'd like to output.
>
> In JS, the "replacer" parameter can be either a JS function or an
> array of property names. I don't think the former is really possible
> (in a SQL callable function) but the latter would be a text[]. The
> "space" parameter can be either a string (used directly) or a number
> (corresponding number of spaces).
>
> The PG function signatures would be something like:
>
> CREATE OR REPLACE FUNCTION jsonb_stringify(obj jsonb, replacer text[],
> space text)
> CREATE OR REPLACE FUNCTION jsonb_stringify(obj jsonb, replacer text[],
> space int)
>
> For convenience we could also include overloads with replacer removed
> (since most people probably want the entire object):
>
> CREATE OR REPLACE FUNCTION jsonb_stringify(obj jsonb, space text)
> CREATE OR REPLACE FUNCTION jsonb_stringify(obj jsonb, space int)
>
> Having json_stringify versions of these would be useful as well.
>


I think if you want these things, especially the filtering, you should 
probably load PLV8.

We could probably do the rest, but I'm not sure it's worth doing given 
that PLV8 is available for all of it.

cheers

andrew

On 02/14/2015 10:06 PM, Andrew Dunstan wrote:
>
> Attached is a patch to provide a number of very useful facilities to
> jsonb that people have asked for. These are based on work by Dmitry
> Dolgov in his jsonbx extension, but I take responsibility for any bugs.
>
> The facilities are:
>
> new operations:
>
> concatenation:    jsonb || jsonb -> jsonb
> deletion:         jsonb - text -> jsonb
> deletion:         jsonb - int -> text
>
> new functions:
>
> produce indented text: jsonb_indent(jsonb) -> text
> change an element at a path:  jsonb_replace(jsonb, text[], jsonb) ->
> jsonb.
>
>
> It would be relatively trivial to add:
>
> delete an element at a path: jsonb_delete(jsonb, text[]) -> json
>
> and I think we should do that for the sake of completeness.
>
> The docs might need a little extra work, and the indent code
> definitely needs work, which I hope to complete in the next day or
> two, but I wanted to put a stake in the ground.
>


In this version the indent code now works correctly, and there is an
additional delete operator:

     jsonb - text[] -> jsonb

Which deletes data at the designated path.

cheers

andrew

Hi,

I looked at the patch and have several comments.

First let me say that modifying the individual paths of the json is the 
feature I miss the most in the current implementation so I am happy that 
this patch was submitted.

I would prefer slightly the patch split into two parts, one for the 
indent printing and one with the manipulation functions, but it's not 
too big patch so it's not too bad as it is.

There is one compiler warning that I see:
jsonfuncs.c:3533:1: warning: no previous prototype for 
‘jsonb_delete_path’ [-Wmissing-prototypes] jsonb_delete_path(PG_FUNCTION_ARGS)

I think it would be better if the ident printing didn't put the start of 
array ([) and start of dictionary ({) on separate line since most 
"pretty" printing implementations outside of Postgres (like the 
JSON.stringify or python's json module) don't do that. This is purely 
about consistency with the rest of the world.

The json_ident might be better named as json_pretty perhaps?

I don't really understand the point of h_atoi() function. What's wrong 
with using strtol like pg_atoi does? Also there is no integer overflow 
check anywhere in that function.

There is tons of end of line whitespace mess in jsonb_indent docs.

Otherwise everything I tried so far works as expected. The code looks ok 
as well except maybe the replacePath could use couple of comments (for 
example the line which uses the h_atoi) to make it easier to follow.

About the:
> +    /* XXX : why do we need this assertion? The functions is declared to take text[] */
> +    Assert(ARR_ELEMTYPE(path) == TEXTOID);

I wonder about this also, some functions do that, some don't, I am not 
really sure what is the rule there myself.

--  Petr Jelinek                  http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training &
Services

> Is there a way to take the json:
> 
> '{"a": 1, "b": 2, "c": {"type": "json", "stuff": "test"}, "d":
> ["aa","bb","cc","dd"]}'
> 
> and add "ee" to "d" without replacing it?  I can think of ways of
> currently doing it, but it's very convoluted just for pushing a value to
> an array.

Can you think of a reasonable syntax for doing that via operators?  I
can imagine that as a json_path function, i.e.:

jsonb_add_to_path(jsonb, text[], jsonb)

or where the end of the path is an array:

jsonb_add_to_path(jsonb, text[], text|int|float|bool)

But I simply can't imagine an operator syntax which would make it clear
what the user intended.

-- 
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com

On 02/25/2015 03:13 AM, Thom Brown wrote:
>     Can you think of a reasonable syntax for doing that via operators?  I
>     can imagine that as a json_path function, i.e.:
> 
>     jsonb_add_to_path(jsonb, text[], jsonb)
> 
>     or where the end of the path is an array:
> 
>     jsonb_add_to_path(jsonb, text[], text|int|float|bool)
> 
>     But I simply can't imagine an operator syntax which would make it clear
>     what the user intended.
> 
> 
> No, there probably isn't a sane operator syntax for such an operation. 
> A function would be nice.  I'd just want to avoid hacking away at arrays
> by exploding them, adding a value then re-arraying them and replacing
> the value.

Well, anyway, that doesn't seem like a reason to block the patch.
Rather, it's a reason to create another one for 9.6 ...


-- 
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com

On 02/26/2015 07:25 AM, Thom Brown wrote:
> Yeah, I think that may be problematic.  I agree with Josh that there's
> probably no sane mix of operators for this, as I would expect your
> example to replace "d": ["aa","bb","cc","dd"] with "d": ["ee"] rather
> than append to it.  Hmm... unless we used a + operator, but then I'm not
> sure what should happen in the instance of '{"d": ["aa"]}' + '{"d": "bb"}'.

Yeah, that's what I would expect too.  In fact, I could would count on
replacement.

-- 
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com

On 23/02/15 18:15, Dmitry Dolgov wrote:
> Hi, Petr, thanks for the review.
>
>  >>> I think it would be better if the ident printing didn't put the
> start of array ([) and start of dictionary ({) on separate line
> Did you mean this?
>
>      [
>          {
>              "a": 1,
>              "b": 2
>          }
>      ]
>
> I tried to verify this in several ways (http://jsonprettyprint.com/,
> "JSON.stringify", "json.too" from python), and I always get this result
> (new line after ([) and ({) ).

No, I mean new lines before the ([) and ({) - try pretty printing 
something like '{"a":["b", "c"], "d": {"e":"f"}}' using that tool you 
pasted and see what your patch outputs to see what I mean.


--  Petr Jelinek                  http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training &
Services

On 03/01/2015 05:03 AM, Petr Jelinek wrote:
> On 23/02/15 18:15, Dmitry Dolgov wrote:
>> Hi, Petr, thanks for the review.
>>
>>  >>> I think it would be better if the ident printing didn't put the
>> start of array ([) and start of dictionary ({) on separate line
>> Did you mean this?
>>
>>      [
>>          {
>>              "a": 1,
>>              "b": 2
>>          }
>>      ]
>>
>> I tried to verify this in several ways (http://jsonprettyprint.com/,
>> "JSON.stringify", "json.too" from python), and I always get this result
>> (new line after ([) and ({) ).
>
> No, I mean new lines before the ([) and ({) - try pretty printing
> something like '{"a":["b", "c"], "d": {"e":"f"}}' using that tool you
> pasted and see what your patch outputs to see what I mean.
>
>


Please try this patch and see if it's doing what you want.

cheers

andrew

On Sat, 14 Feb 2015 22:06:07 -0500
Andrew Dunstan <andrew@dunslane.net> wrote:

Hello.

I have function with recursive merging objects:

# SELECT jsonb_deep_extend('{"a": {"b": 6}}'::jsonb, '{"a": {"c":
7}}'::jsonb) AS new_jsonb;
new_jsonb        
-------------------------{"a": {"b": 6, "c": 7}}

https://github.com/koctep/jsonb-extend

I think this function may be useful for people too.

Could you add it to your patch? I don't have enough time to prepare
patch.

> 
> Attached is a patch to provide a number of very useful facilities to 
> jsonb that people have asked for. These are based on work by Dmitry 
> Dolgov in his jsonbx extension, but I take responsibility for any
> bugs.
> 
> The facilities are:
> 
> new operations:
> 
> concatenation:    jsonb || jsonb -> jsonb
> deletion:         jsonb - text -> jsonb
> deletion:         jsonb - int -> text
> 
> new functions:
> 
> produce indented text: jsonb_indent(jsonb) -> text
> change an element at a path:  jsonb_replace(jsonb, text[], jsonb) ->
> jsonb.
> 
> 
> It would be relatively trivial to add:
> 
> delete an element at a path: jsonb_delete(jsonb, text[]) -> json
> 
> and I think we should do that for the sake of completeness.
> 
> The docs might need a little extra work, and the indent code
> definitely needs work, which I hope to complete in the next day or
> two, but I wanted to put a stake in the ground.
> 
> 
> cheers
> 
> andrew
> 

On 03/11/2015 04:05 AM, Ilya Ashchepkov wrote:
> On Sat, 14 Feb 2015 22:06:07 -0500
> Andrew Dunstan <andrew@dunslane.net> wrote:
>
> Hello.
>
> I have function with recursive merging objects:
>
> # SELECT jsonb_deep_extend('{"a": {"b": 6}}'::jsonb, '{"a": {"c":
> 7}}'::jsonb) AS new_jsonb;
> new_jsonb
> -------------------------
>   {"a": {"b": 6, "c": 7}}
>
> https://github.com/koctep/jsonb-extend
>
> I think this function may be useful for people too.
>
> Could you add it to your patch? I don't have enough time to prepare
> patch.


It's far too late to be adding new material.

cheers

andrew

On 01/03/15 16:49, Andrew Dunstan wrote:
>
> On 03/01/2015 05:03 AM, Petr Jelinek wrote:
>> On 23/02/15 18:15, Dmitry Dolgov wrote:
>>> Hi, Petr, thanks for the review.
>>>
>>>  >>> I think it would be better if the ident printing didn't put the
>>> start of array ([) and start of dictionary ({) on separate line
>>> Did you mean this?
>>>
>>>      [
>>>          {
>>>              "a": 1,
>>>              "b": 2
>>>          }
>>>      ]
>>>
>>> I tried to verify this in several ways (http://jsonprettyprint.com/,
>>> "JSON.stringify", "json.too" from python), and I always get this result
>>> (new line after ([) and ({) ).
>>
>> No, I mean new lines before the ([) and ({) - try pretty printing
>> something like '{"a":["b", "c"], "d": {"e":"f"}}' using that tool you
>> pasted and see what your patch outputs to see what I mean.
>>
>>
>
>
> Please try this patch and see if it's doing what you want.
>

Yes, this produces output that looks like what javascript/python produce.


(the other stuff I commented about, mainly the h_atoi is still unfixed 
though)

--  Petr Jelinek                  http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training &
Services

On 18/04/15 20:35, Dmitry Dolgov wrote:
> Sorry for late reply. Here is a slightly improved version of the patch
> with the new `h_atoi` function, I hope this implementation will be more
> appropriate.
>

It's better, but a) I don't like the name of the function b) I don't see 
why we need the function at all given that it's called from only one 
place and is effectively couple lines of code.

In general I wonder if it wouldn't be better to split the replacePath 
into 3 functions, one replacePath, one replacePathObject, 
replacePathArray and call those Object/Array ones from the replacePath 
and inlining the h_atoi code into the Array one. If this was done then 
it would make also sense to change the main if/else in replacePath into 
a switch.

Another thing I noticed now when reading the code again - you should not 
be using braces when you only have one command in the code-block.

--  Petr Jelinek                  http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training &
Services

On 04/27/2015 12:46 PM, Petr Jelinek wrote:
>
> Another thing I noticed now when reading the code again - you should 
> not be using braces when you only have one command in the code-block.
>

There's one exception I, at least, have to this rule, namely when 
there's a corresponding compound if or else. I personally find this 
unaesthetic to put it mildly:
   if (condition)       statement;   else   {       block of statements;   }



pgindent used to produce stuff like this, and you had to put a comment 
in the block to get around it, but we stopped that years ago, IIRC.

cheers

andrew

On Mon, Apr 27, 2015 at 6:41 PM, Andrew Dunstan <andrew@dunslane.net> wrote:
> There's one exception I, at least, have to this rule, namely when there's a
> corresponding compound if or else. I personally find this unaesthetic to put
> it mildly:
>
>    if (condition)
>        statement;
>    else
>    {
>        block of statements;
>    }

Hmm, I don't dislike that style.  If somebody submitted a patch with
braces around the lone statement, I would remove them before
committing.

<ducks>

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Robert Haas wrote:
> On Mon, Apr 27, 2015 at 6:41 PM, Andrew Dunstan <andrew@dunslane.net> wrote:
> > There's one exception I, at least, have to this rule, namely when there's a
> > corresponding compound if or else. I personally find this unaesthetic to put
> > it mildly:
> >
> >    if (condition)
> >        statement;
> >    else
> >    {
> >        block of statements;
> >    }
> 
> Hmm, I don't dislike that style.  If somebody submitted a patch with
> braces around the lone statement, I would remove them before
> committing.

Same here.

-- 
Álvaro Herrera                http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

* Alvaro Herrera (alvherre@2ndquadrant.com) wrote:
> Robert Haas wrote:
> > On Mon, Apr 27, 2015 at 6:41 PM, Andrew Dunstan <andrew@dunslane.net> wrote:
> > > There's one exception I, at least, have to this rule, namely when there's a
> > > corresponding compound if or else. I personally find this unaesthetic to put
> > > it mildly:
> > >
> > >    if (condition)
> > >        statement;
> > >    else
> > >    {
> > >        block of statements;
> > >    }
> >
> > Hmm, I don't dislike that style.  If somebody submitted a patch with
> > braces around the lone statement, I would remove them before
> > committing.
>
> Same here.

Agreed.
Thanks!
    Stephen

On 04/29/2015 01:19 PM, Robert Haas wrote:
> On Mon, Apr 27, 2015 at 6:41 PM, Andrew Dunstan <andrew@dunslane.net> wrote:
>> There's one exception I, at least, have to this rule, namely when there's a
>> corresponding compound if or else. I personally find this unaesthetic to put
>> it mildly:
>>
>>     if (condition)
>>         statement;
>>     else
>>     {
>>         block of statements;
>>     }
> Hmm, I don't dislike that style.  If somebody submitted a patch with
> braces around the lone statement, I would remove them before
> committing.
>
> <ducks>
>

It's a matter of taste, but I find things a lot easier to understand 
when they are symmetrical. Thus I like all the branches of an "if" to be 
either in a block or not, and I like braces to line up either 
horizontally or vertically. Perhaps this reflects my history, where I 
wrote huge amounts of Ada and other non-C-like languages, well before I 
ever wrote lots of C or C-ish languages.


Another case where I think putting a single statement in a block makes 
sense is where the condition of the "if" spreads across more than one 
line. This works particularly well with our BSD style brace placement.

cheers

andrew

Andrew Dunstan <andrew@dunslane.net> wrote:

> It's a matter of taste, but I find things a lot easier to
> understand when they are symmetrical. Thus I like all the
> branches of an "if" to be either in a block or not, and I like
> braces to line up either horizontally or vertically. Perhaps this
> reflects my history, where I wrote huge amounts of Ada and other
> non-C-like languages, well before I ever wrote lots of C or C-ish
> languages.
>
> Another case where I think putting a single statement in a block
> makes sense is where the condition of the "if" spreads across
> more than one line. This works particularly well with our BSD
> style brace placement.

My personal preferences are the same on all of that, especially
that the closing paren, brace, or bracket should be either in the
same line or the same column as its mate.  If we were going to open
a green-field discussion about what style to *choose* I would be
arguing for all of the above (plus a few other things which are not
current PostgreSQL style).

That said, I feel very strongly that it is important that everyone
use the *same* style.  It is far more important to me that we stick
to a single style than that the style match my personal
preferences.  The project style seems to me to be that a single
statement is not put into braces unless needed for correctness or
to prevent warnings about ambiguity from the compilers.

By the way, my preference for the above are not strong enough to
want to open up the style choices to re-evaluation.  PLEASE, no!

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On April 29, 2015 03:09:51 PM Andrew Dunstan wrote:
> On 04/29/2015 01:19 PM, Robert Haas wrote:
> > On Mon, Apr 27, 2015 at 6:41 PM, Andrew Dunstan <andrew@dunslane.net> 
wrote:
> >> There's one exception I, at least, have to this rule, namely when there's
> >> a
> >> corresponding compound if or else. I personally find this unaesthetic to
> >> put>> 
> >> it mildly:
> >>     if (condition)
> >>     
> >>         statement;
> >>     
> >>     else
> >>     {
> >>     
> >>         block of statements;
> >>     
> >>     }
> > 
> > Hmm, I don't dislike that style.  If somebody submitted a patch with
> > braces around the lone statement, I would remove them before
> > committing.
> > 
> > <ducks>
> 
> It's a matter of taste, but I find things a lot easier to understand
> when they are symmetrical. Thus I like all the branches of an "if" to be
> either in a block or not, and I like braces to line up either
> horizontally or vertically. Perhaps this reflects my history, where I
> wrote huge amounts of Ada and other non-C-like languages, well before I
> ever wrote lots of C or C-ish languages.
> 
> 
> Another case where I think putting a single statement in a block makes
> sense is where the condition of the "if" spreads across more than one
> line. This works particularly well with our BSD style brace placement.

I'm sure that many, many bits have been spilled over this, reaching way back 
into the stone age of computing, sometimes almost reaching emacs-vs-vi levels 
of intensity.

My position is the better-safe-than-sorry corner, which says to always add 
braces, even if there's only one statement. Because one day somebody will be 
in a rush, and will add a second statement without adding the braces, and 
things will explode horribly.

But that's just me.

jan

On 27/04/15 18:46, Petr Jelinek wrote:
> On 18/04/15 20:35, Dmitry Dolgov wrote:
>> Sorry for late reply. Here is a slightly improved version of the patch
>> with the new `h_atoi` function, I hope this implementation will be more
>> appropriate.
>>
>
> It's better, but a) I don't like the name of the function b) I don't see
> why we need the function at all given that it's called from only one
> place and is effectively couple lines of code.
>
> In general I wonder if it wouldn't be better to split the replacePath
> into 3 functions, one replacePath, one replacePathObject,
> replacePathArray and call those Object/Array ones from the replacePath
> and inlining the h_atoi code into the Array one. If this was done then
> it would make also sense to change the main if/else in replacePath into
> a switch.
>
> Another thing I noticed now when reading the code again - you should not
> be using braces when you only have one command in the code-block.
>

Hi,

I worked this over a bit (I hope Dmitry won't mind) and I am now more or
less happy with the patch. Here is list of changes I made:
- rebased to todays master (Oid conflicts, transforms patch conflicts)
- changed the main if/else if/else if/else to switch in replacePath
- split the replacePath into 3 functions (main one plus 2 helpers for
Object and Array)
- removed the h_atoi and use the strtol directly
- renamed jsonb_indent to jsonb_pretty because we use "pretty" for
similar use-case everywhere else
- fixed whitespace/brackets where needed
- added/reworded some comments and couple of lines in docs

I think it's ready for Andrew now.

--
  Petr Jelinek                  http://www.2ndQuadrant.com/
  PostgreSQL Development, 24x7 Support, Training & Services

Yeaha didn't work either on  http://jsonprettyprint.net
<http://jsonprettyprint.net>   for me.



--
View this message in context:
http://postgresql.nabble.com/mogrify-and-indent-features-for-jsonb-tp5838008p5848933.html
Sent from the PostgreSQL - hackers mailing list archive at Nabble.com.

I would like to suggest https://jsonformatter.org/json-pretty-print for
formatting and beautifying JSON data. 

Also for use this validationg JSON data, https://jsonformatter.org 



--
Sent from: http://www.postgresql-archive.org/PostgreSQL-hackers-f1928748.html

On 04/30/2018 05:33 AM, jamesmalvi wrote:
> I would like to suggest https://jsonformatter.org/json-pretty-print for
> formatting and beautifying JSON data. 
>
> Also for use this validationg JSON data, https://jsonformatter.org 
>
>


We already have a pretty-print function built in, and we already use a
validating parser. So it's not clear to me what you want that we don't have.


cheers

andrew

-- 
Andrew Dunstan                https://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services