Re: [HACKERS] mogrify and indent features for jsonb

[Eva7]

Yeaha didn't work either on  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.


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] mogrify and indent features for jsonb

[Petr Jelinek]

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
From b2da4958fc75e7c7009340e6b2e4ccb155632078 Mon Sep 17 00:00:00 2001
From: Petr Jelinek 
Date: Wed, 29 Apr 2015 22:54:01 +0200
Subject: [PATCH] jsonbxcore5

---
 doc/src/sgml/func.sgml|  62 +++
 src/backend/utils/adt/jsonb.c |  81 +++-
 src/backend/utils/adt/jsonfuncs.c | 709 ++
 src/include/catalog/pg_operator.h |   8 +
 src/include/catalog/pg_proc.h |   9 +-
 src/include/utils/jsonb.h |  19 +-
 src/test/regress/expected/jsonb.out   | 424 +++-
 src/test/regress/expected/jsonb_1.out | 424 +++-
 src/test/regress/sql/jsonb.sql|  85 +++-
 9 files changed, 1805 insertions(+), 16 deletions(-)

diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index dcade93..f0d8d96 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -10257,6 +10257,32 @@ table2-mapping
 Do all of these key/element strings exist?
 '["a", "b"]'::jsonb ?& array['a', 'b']

+   
+||
+jsonb
+Concatentate two jsonb values into a new jsonb value
+'["a", "b"]'::jsonb || '["c", "d"]'::jsonb
+   
+   
+-
+text
+Delete the field with a specified key, or element with this
+value
+'{"a": "b"}'::jsonb - 'a' 
+   
+   
+-
+integer
+Delete the field or element with specified index (Negative
+integers count from the end)
+'["a", "b"]'::jsonb - 1 
+   
+   
+-
+text[]
+Delete the field or element with specified path
+'["a", {"b":1}]'::jsonb - '{1,b}'::text[] 
+   
   
  

@@ -10767,6 +10793,42 @@ table2-mapping
json_strip_nulls('[{"f1":1,"f2":null},2,null,3]')
[{"f1":1},2,null,3]

+  
+   jsonb_replace(target jsonb, path text[], replacement jsonb)
+ 
+   jsonb
+   
+ Returns target
+ with the section designated by  path
+ replaced by replacement.
+   
+   jsonb_replace('[{"f1":1,"f2":null},2,null,3]', '{0,f1}','[2,3,4]')
+   [{"f1":[2,3,4],"f2":null},2,null,3]
+
+   
+  
+   jsonb_pretty(from_json jsonb)
+ 
+   text
+   
+ Returns from_json
+ as indented json text.
+   
+   jsonb_pretty('[{"f1":1,"f2":null},2,null,3]')
+   
+
+ [
+ {
+ "f1": 1,
+ "f2": null
+ },
+ 2,
+ null,
+ 3
+ ]
+
+
+   
  
 

diff --git a/src/backend/utils/adt/jsonb.c b/src/backend/utils/adt/jsonb.c
index 7e2c359..bccc669 100644
--- a/src/backend/utils/adt/jsonb.c
+++ b/src/backend/utils/adt/jsonb.c
@@ -85,6 +85,8 @@ static void datum_to_jsonb(Datum val, bool is_null, JsonbInState *result,
 static void add_jsonb(Datum val, bool is_null, JsonbInState *result,
 		  Oid val_type, bool key_scalar);
 static JsonbParseState * clone_parse_state(JsonbParseState * state);
+static char *JsonbToCStringWorker(StringInfo out, JsonbContainer *in, int estimated_len, bool indent);
+static void add_indent(StringInfo out, bool indent, int level);
 
 /*
  * jsonb type input function
@@ -422,12 +424,39 @@ jsonb_in_scalar(void *pstate, char *token, JsonTokenTyp

Re: [HACKERS] mogrify and indent features for jsonb

[Jan de Visser]

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  
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.
> > 
> > 
> 
> 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



-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] mogrify and indent features for jsonb

[Kevin Grittner]

Andrew Dunstan  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


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] mogrify and indent features for jsonb

[Andrew Dunstan]

On 04/29/2015 01:19 PM, Robert Haas wrote:

On Mon, Apr 27, 2015 at 6:41 PM, Andrew Dunstan  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.





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


--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] mogrify and indent features for jsonb

[Stephen Frost]

* Alvaro Herrera (alvhe...@2ndquadrant.com) wrote:
> Robert Haas wrote:
> > On Mon, Apr 27, 2015 at 6:41 PM, Andrew Dunstan  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


signature.asc
Description: Digital signature

Re: [HACKERS] mogrify and indent features for jsonb

[Alvaro Herrera]

Robert Haas wrote:
> On Mon, Apr 27, 2015 at 6:41 PM, Andrew Dunstan  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 Herrerahttp://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] mogrify and indent features for jsonb

[Robert Haas]

On Mon, Apr 27, 2015 at 6:41 PM, Andrew Dunstan  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.



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


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] mogrify and indent features for jsonb

[Andrew Dunstan]

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



--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] mogrify and indent features for jsonb

[Petr Jelinek]

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


--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] mogrify and indent features for jsonb

[Dmitry Dolgov]

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.

On 13 March 2015 at 23:30, Petr Jelinek  wrote:

> 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
>


jsonbxcore4.patch
Description: Binary data

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] mogrify and indent features for jsonb

[Petr Jelinek]

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


--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] mogrify and indent features for jsonb

[Andrew Dunstan]

On 03/11/2015 04:05 AM, Ilya Ashchepkov wrote:

On Sat, 14 Feb 2015 22:06:07 -0500
Andrew Dunstan  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




--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] mogrify and indent features for jsonb

[Ilya Ashchepkov]

On Sat, 14 Feb 2015 22:06:07 -0500
Andrew Dunstan  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
> 




-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] mogrify and indent features for jsonb

[Andrew Dunstan]

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


diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index da2ed67..01e53e8 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -10256,6 +10256,30 @@ table2-mapping
 Do all of these key/element strings exist?
 '["a", "b"]'::jsonb ?& array['a', 'b']

+   
+||
+jsonb
+Concatentate these two values to make a new value
+'["a", "b"]'::jsonb || '["c", "d"]'::jsonb
+   
+   
+-
+text
+Delete the field with this key, or element with this value
+'{"a": "b"}'::jsonb - 'a' 
+   
+   
+-
+integer
+Delete the field or element with this index (Negative integers count from the end)
+'["a", "b"]'::jsonb - 1 
+   
+   
+-
+text[]
+Delete the field or element with this path
+'["a", {"b":1}]'::jsonb - '{1,b}'::text[] 
+   
   
  

@@ -10766,6 +10790,42 @@ table2-mapping
json_strip_nulls('[{"f1":1,"f2":null},2,null,3]')
[{"f1":1},2,null,3]

+  
+   jsonb_replace(target jsonb, path text[], replacement jsonb)
+ 
+   jsonb
+   
+ Returns target
+ with the section designated by  path
+ replaced by replacement.
+   
+   jsonb_replace('[{"f1":1,"f2":null},2,null,3]', '{0,f1}','[2,3,4]')
+   [{"f1":[2,3,4],"f2":null},2,null,3]
+
+   
+  
+   jsonb_indent(from_json jsonb)
+ 
+   text
+   
+ Returns from_json
+ as indented json text.
+   
+   jsonb_indent('[{"f1":1,"f2":null},2,null,3]')
+   
+
+ [
+ {
+ "f1": 1,
+ "f2": null
+ },
+ 2,
+ null,
+ 3
+ ]
+
+
+   
  
 

diff --git a/src/backend/utils/adt/jsonb.c b/src/backend/utils/adt/jsonb.c
index 5833401..6a92305 100644
--- a/src/backend/utils/adt/jsonb.c
+++ b/src/backend/utils/adt/jsonb.c
@@ -85,6 +85,8 @@ static void datum_to_jsonb(Datum val, bool is_null, JsonbInState *result,
 static void add_jsonb(Datum val, bool is_null, JsonbInState *result,
 		  Oid val_type, bool key_scalar);
 static JsonbParseState * clone_parse_state(JsonbParseState * state);
+static char *JsonbToCStringWorker(StringInfo out, JsonbContainer *in, int estimated_len, bool indent);
+static void add_indent(StringInfo out, bool indent, int level);
 
 /*
  * jsonb type input function
@@ -422,12 +424,41 @@ jsonb_in_scalar(void *pstate, char *token, JsonTokenType tokentype)
 char *
 JsonbToCString(StringInfo out, JsonbContainer *in, int estimated_len)
 {
+	return JsonbToCStringWorker(out, in, estimated_len, false);
+}
+
+/*
+ * same thing but with indentation turned on
+ */
+
+char *
+JsonbToCStringIndent(StringInfo out, JsonbContainer *in, int estimated_len)
+{
+	return JsonbToCStringWorker(out, in, estimated_len, true);
+}
+
+
+/*
+ * common worker for above two functions
+ */
+static char *
+JsonbToCStringWorker(StringInfo out, JsonbContainer *in, int estimated_len, bool indent)
+{
 	bool		first = true;
 	JsonbIterator *it;
 	JsonbIteratorToken type = WJB_DONE;
 	JsonbValue	v;
 	int			level = 0;
 	bool		redo_switch = false;
+	/* If we are indenting, don't add a space after a comma */
+	int			ispaces = indent ? 1 : 2;
+	/*
+	 * Don't indent the very first item. This gets set to the indent flag
+	 * at the bottom of the loop.
+	 */
+	booluse_indent = false;
+	boolraw_scalar = false;
+	boollast_was_key = false;
 
 	if (out == NULL)
 		out = makeStringInfo();
@@ -444,26 +475,37 @@ JsonbToCString(StringInfo out, JsonbContainer *in, int estimated_len)
 		{
 			case WJB_BEGIN_ARRAY:
 if (!first)
-	appendBinaryStringInfo(out, ", ", 2);
-first = true;
+	appendBinaryStringInfo(out, ", ", ispaces);
 
 if (!v.val.array.rawScalar)
-	appendStringInfoChar(out, '[');
+{
+	add_indent(out, use_indent && !last_was_key, level);
+	appendStringInfoCharMacro(out, '[');
+}
+else
+{
+	raw_scalar = true;
+}
+first = true;
 level++;
 break;
 			case WJB_BEGIN_OBJECT:
 if (!first)
-	appendBinaryStringInfo(out, "

Re: [HACKERS] mogrify and indent features for jsonb

[Petr Jelinek]

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


--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] mogrify and indent features for jsonb

[Josh Berkus]

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


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] mogrify and indent features for jsonb

[Thom Brown]

On 26 February 2015 at 15:09, Dmitry Dolgov <9erthali...@gmail.com> wrote:

> Hi, Thom.
>
> > Would this support deleting "type" and the value 'dd'
>
> With this patch you can delete them one by one:
>
>  select '{"a": 1, "b": 2, "c": {"type": "json", "stuff": "test"}, "d":
> ["aa","bb","cc","dd"]}'::jsonb - '{c, type}'::text[] - '{d, -1}'::text[];
>  ?column?
> ---
>  {"a": 1, "b": 2, "c": {"stuff": "test"}, "d": ["aa", "bb", "cc"]}
> (1 row)
>

Doesn't work for me:

# select '{"a": 1, "b": 2, "c": {"type": "json", "stuff": "test"}, "d":
["aa","bb","cc","dd"]}'::jsonb - '{c, type}'::text[] - '{d, -1}'::text[];
ERROR:  operator does not exist: jsonb - text[]
LINE 1: ...ff": "test"}, "d": ["aa","bb","cc","dd"]}'::jsonb - '{c, typ...
 ^
HINT:  No operator matches the given name and argument type(s). You might
need to add explicit type casts.


> > 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?
>
> No, looks like there is no way to add a new element to array with help of
> this patch. I suppose this feature can be implemented easy enough inside
> the "jsonb_concat" function:
>
>  select '{"a": 1, "b": 2, "c": {"type": "json", "stuff": "test"}, "d":
> ["aa","bb","cc","dd"]}'::jsonb || '{"d": ["ee"]}'::jsonb
>
> but I'm not sure, that it will be the best way.
>

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"}'.

-- 
Thom

Re: [HACKERS] mogrify and indent features for jsonb

[Dmitry Dolgov]

Hi, Thom.

> Would this support deleting "type" and the value 'dd'

With this patch you can delete them one by one:

 select '{"a": 1, "b": 2, "c": {"type": "json", "stuff": "test"}, "d":
["aa","bb","cc","dd"]}'::jsonb - '{c, type}'::text[] - '{d, -1}'::text[];
 ?column?
---
 {"a": 1, "b": 2, "c": {"stuff": "test"}, "d": ["aa", "bb", "cc"]}
(1 row)


> 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?

No, looks like there is no way to add a new element to array with help of
this patch. I suppose this feature can be implemented easy enough inside
the "jsonb_concat" function:

 select '{"a": 1, "b": 2, "c": {"type": "json", "stuff": "test"}, "d":
["aa","bb","cc","dd"]}'::jsonb || '{"d": ["ee"]}'::jsonb

but I'm not sure, that it will be the best way.


On 26 February 2015 at 01:13, Josh Berkus  wrote:

> 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
>
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
>

Re: [HACKERS] mogrify and indent features for jsonb

[Josh Berkus]

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


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] mogrify and indent features for jsonb

[Thom Brown]

On 24 February 2015 at 19:16, Josh Berkus  wrote:

>
> > 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.
>

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.

-- 
Thom

Re: [HACKERS] mogrify and indent features for jsonb

[Josh Berkus]

> 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


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] mogrify and indent features for jsonb

[Thom Brown]

On 15 February 2015 at 03:06, 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
>

Would this support deleting "type" and the value 'dd' from the following?:

{"a": 1, "b": 2, "c": {"type": "json", "stuff": "test"}, "d":
["aa","bb","cc","dd"]}


>
> 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.
>

This is high on my wanted list, so thanks for working on this.

Seems to work well for me with a few tests.

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.

Also, are there any plans to support the following?:

jsonb - text[] # Provide list of keys to delete in array
jsonb - jsonb # Deduplicate key:value pairs
jsonb && jsonb # Return overlapping jsonb (opposite of jsonb - jsonb)

Thanks

Thom

Re: [HACKERS] mogrify and indent features for jsonb

[Dmitry Dolgov]

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 ({) ).

>>> I don't really understand the point of h_atoi() function.
Initially, this function was to combine the convertion logic and specific
verifications. But I agree, "strtol" is more correct way, I should improve
this.

>>> The code looks ok as well except maybe the replacePath could use couple
of comments
I already added several commentaries (and looks like I should add even more
in the nearest future) for this function in the jsonbx extension, and I
think we can update this patch one more time with that improvement.

>>> About the Assert(ARR_ELEMTYPE(path) == TEXTOID);
I based my work on the hstore extension, which contains such kind of
assertions. But I suppose, it's not required anymore, so I removed this
from the extension. And, I think, we can also remove this from patch.

On 18 February 2015 at 08:32, Petr Jelinek  wrote:

> 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
>
>
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
>

Re: [HACKERS] mogrify and indent features for jsonb

[Petr Jelinek]

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


--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] mogrify and indent features for jsonb

[Andrew Dunstan]

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

diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index d57243a..9936bff 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -10256,6 +10256,30 @@ table2-mapping
 Do all of these key/element strings exist?
 '["a", "b"]'::jsonb ?& array['a', 'b']

+   
+||
+jsonb
+Concatentate these two values to make a new value
+'["a", "b"]'::jsonb || '["c", "d"]'::jsonb
+   
+   
+-
+text
+Delete the field with this key, or element with this value
+'{"a": "b"}'::jsonb - 'a' 
+   
+   
+-
+integer
+Delete the field or element with this index (Negative integers count from the end)
+'["a", "b"]'::jsonb - 1 
+   
+   
+-
+text[]
+Delete the field or element with this path
+'["a", {"b":1}]'::jsonb - '{1,b}'::text[] 
+   
   
  

@@ -10760,6 +10784,42 @@ table2-mapping
json_strip_nulls('[{"f1":1,"f2":null},2,null,3]')
[{"f1":1},2,null,3]

+  
+   jsonb_replace(target jsonb, path text[], replacement jsonb)
+ 
+   jsonb
+   
+ Returns target
+ with the section designated by  path
+ replaced by replacement.
+   
+   jsonb_replace('[{"f1":1,"f2":null},2,null,3]', '{0,f1},'[2,3,4]')
+   [{"f1":[2,3,4],"f2":null},2,null,3]
+
+   
+  
+   jsonb_indent(from_json jsonb)
+ 
+   text
+   
+ Returns from_json
+ as indented json text.
+   
+   jsonb_indent('[{"f1":1,"f2":null},2,null,3]')
+   
+
+ [  
+ {  
+ "f1": 1,   
+ "f2": null 
+ }, 
+ 2, 
+ null,  
+ 3  
+ ]
+
+
+   
  
 

diff --git a/src/backend/utils/adt/jsonb.c b/src/backend/utils/adt/jsonb.c
index 644ea6d..133b9ba 100644
--- a/src/backend/utils/adt/jsonb.c
+++ b/src/backend/utils/adt/jsonb.c
@@ -77,6 +77,8 @@ static void datum_to_jsonb(Datum val, bool is_null, JsonbInState *result,
 static void add_jsonb(Datum val, bool is_null, JsonbInState *result,
 		  Oid val_type, bool key_scalar);
 static JsonbParseState * clone_parse_state(JsonbParseState * state);
+static char *JsonbToCStringWorker(StringInfo out, JsonbContainer *in, int estimated_len, bool indent);
+static void add_indent(StringInfo out, bool indent, int level);
 
 /*
  * jsonb type input function
@@ -414,12 +416,40 @@ jsonb_in_scalar(void *pstate, char *token, JsonTokenType tokentype)
 char *
 JsonbToCString(StringInfo out, JsonbContainer *in, int estimated_len)
 {
+	return JsonbToCStringWorker(out, in, estimated_len, false);
+}
+
+/*
+ * same thing but with indentation turned on
+ */
+
+char *
+JsonbToCStringIndent(StringInfo out, JsonbContainer *in, int estimated_len)
+{
+	return JsonbToCStringWorker(out, in, estimated_len, true);
+}
+
+
+/*
+ * common worker for above two functions
+ */
+static char *
+JsonbToCStringWorker(StringInfo out, JsonbContainer *in, int estimated_len, bool indent)
+{
 	bool		first = true;
 	JsonbIterator *it;
 	int			type = 0;
 	JsonbValue	v;
 	int			level = 0;
 	bool		redo_switch = false;
+	/* If we are indenting, don't add a space after a comma */
+	int			ispaces = indent ? 1 : 2;
+	/*
+	 * Don't indent the very first item. This gets set to the indent flag
+	 * at the bottom of the loop.
+	 */
+	booluse_indent = false;
+	boolraw_scalar = false;
 
 	if (out == NULL)
 		out = makeStringInfo();
@@ -436,26 +466,37 @@ JsonbToCString(StringInfo out, JsonbContainer *in, int estimated_len)
 		{
 			case WJB_BEGIN_ARRAY:
 if (!first)
-	appendBinaryStringInfo(out, ", ", 2);
+	appendBinaryStringInfo(out, ", ", ispaces);
 first = true;
 
 if (!v.val.array.rawScala

Re: [HACKERS] mogrify and indent features for jsonb

[Andrew Dunstan]

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



--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] mogrify and indent features for jsonb

[Sehrope Sarkuni]

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


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

[HACKERS] mogrify and indent features for jsonb

[Andrew Dunstan]

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
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index d57243a..9fdaee7 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -10256,6 +10256,24 @@ table2-mapping
 Do all of these key/element strings exist?
 '["a", "b"]'::jsonb ?& array['a', 'b']

+   
+||
+jsonb
+Concatentate these two values to make a new value
+'["a", "b"]'::jsonb || '["c", "d"]'::jsonb
+   
+   
+-
+text
+Delete the object field with this key
+'{"a": "b"}'::jsonb - 'a' 
+   
+   
+-
+integer
+Delete the element with this index (Negative integers count from the end of the array.)
+'["a", "b"]'::jsonb - 1 
+   
   
  

@@ -10760,6 +10778,42 @@ table2-mapping
json_strip_nulls('[{"f1":1,"f2":null},2,null,3]')
[{"f1":1},2,null,3]

+  
+   jsonb_replace(target jsonb, path text[], replacement jsonb)
+ 
+   jsonb
+   
+ Returns target
+ with the section designated by  path
+ replaced by replacement.
+   
+   jsonb_replace('[{"f1":1,"f2":null},2,null,3]', '{0,f1},'[2,3,4]')
+   [{"f1":[2,3,4],"f2":null},2,null,3]
+
+   
+  
+   jsonb_indent(from_json jsonb)
+ 
+   text
+   
+ Returns from_json
+ as indented json text.
+   
+   jsonb_indent('[{"f1":1,"f2":null},2,null,3]')
+   
+
+ [  
+ {  
+ "f1": 1,   
+ "f2": null 
+ }, 
+ 2, 
+ null,  
+ 3  
+ ]
+
+
+   
  
 

diff --git a/src/backend/utils/adt/jsonb.c b/src/backend/utils/adt/jsonb.c
index 644ea6d..4ba4951 100644
--- a/src/backend/utils/adt/jsonb.c
+++ b/src/backend/utils/adt/jsonb.c
@@ -77,6 +77,8 @@ static void datum_to_jsonb(Datum val, bool is_null, JsonbInState *result,
 static void add_jsonb(Datum val, bool is_null, JsonbInState *result,
 		  Oid val_type, bool key_scalar);
 static JsonbParseState * clone_parse_state(JsonbParseState * state);
+static char *JsonbToCStringWorker(StringInfo out, JsonbContainer *in, int estimated_len, bool indent);
+static void add_indent(StringInfo out, bool indent, int level);
 
 /*
  * jsonb type input function
@@ -414,12 +416,25 @@ jsonb_in_scalar(void *pstate, char *token, JsonTokenType tokentype)
 char *
 JsonbToCString(StringInfo out, JsonbContainer *in, int estimated_len)
 {
+	return JsonbToCStringWorker(out, in, estimated_len, false);
+}
+
+char *
+JsonbToCStringIndent(StringInfo out, JsonbContainer *in, int estimated_len)
+{
+	return JsonbToCStringWorker(out, in, estimated_len, true);
+}
+
+static char *
+JsonbToCStringWorker(StringInfo out, JsonbContainer *in, int estimated_len, bool indent)
+{
 	bool		first = true;
 	JsonbIterator *it;
 	int			type = 0;
 	JsonbValue	v;
 	int			level = 0;
 	bool		redo_switch = false;
+	int			ispaces = indent ? 1 : 2;
 
 	if (out == NULL)
 		out = makeStringInfo();
@@ -436,26 +451,34 @@ JsonbToCString(StringInfo out, JsonbContainer *in, int estimated_len)
 		{
 			case WJB_BEGIN_ARRAY:
 if (!first)
-	appendBinaryStringInfo(out, ", ", 2);
+	appendBinaryStringInfo(out, ", ", ispaces);
 first = true;
 
 if (!v.val.array.rawScalar)
-	appendStringInfoChar(out, '[');
+{
+	add_indent(out, indent, level);
+	appendStringInfoCharMacro(out, '[');
+}
 level++;
+add_indent(out, indent, level);
 break;
 			case WJB_BEGIN_OBJECT:
 if (!first)
-	appendBinaryStringInfo(out, ", ", 2);
+	appendBinaryStringInfo(out, ", ", ispaces);
 first = true;
+
+add_indent(out, indent, level);
 appendStringInfoCharMacro(out, '{');
 
 level++;
 break;
 			case WJB_KEY:
 if (!first)
-	appendBinaryStringInfo(out, ", ", 2);
+	appendBinaryStringInfo(out, ", ", ispaces);
 first = true;
 
+add_indent(out, indent, level);
+
 /* json rules guarantee this is a string */
 jsonb_put_escaped_value(out, &v);