Re: [HACKERS] mogrify and indent features for jsonb
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. -- 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 and...@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 -- 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
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 and...@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 -- 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
On Mon, Apr 27, 2015 at 6:41 PM, Andrew Dunstan and...@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 -- 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
* Alvaro Herrera (alvhe...@2ndquadrant.com) wrote: Robert Haas wrote: On Mon, Apr 27, 2015 at 6:41 PM, Andrew Dunstan and...@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 signature.asc Description: Digital signature
Re: [HACKERS] mogrify and indent features for jsonb
Robert Haas wrote: On Mon, Apr 27, 2015 at 6:41 PM, Andrew Dunstan and...@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 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
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 pjmodos@pjmodos.net 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 entryDo all of these key/element emphasisstrings/emphasis exist?/entry entryliteral'[a, b]'::jsonb ?amp; array['a', 'b']/literal/entry /row + row +entryliteral||/literal/entry +entrytypejsonb/type/entry +entryConcatentate two jsonb values into a new jsonb value/entry +entryliteral'[a, b]'::jsonb || '[c, d]'::jsonb/literal/entry + /row + row +entryliteral-/literal/entry +entrytypetext/type/entry +entryDelete the field with a specified key, or element with this +value/entry +entryliteral'{a: b}'::jsonb - 'a' /literal/entry + /row + row +entryliteral-/literal/entry +entrytypeinteger/type/entry +entryDelete the field or element with specified index (Negative +integers count from the end)/entry +entryliteral'[a, b]'::jsonb - 1 /literal/entry + /row + row +entryliteral-/literal/entry +entrytypetext[]/type/entry +entryDelete the field or element with specified path/entry +entryliteral'[a, {b:1}]'::jsonb - '{1,b}'::text[] /literal/entry + /row /tbody /tgroup /table @@ -10767,6 +10793,42 @@ table2-mapping entryliteraljson_strip_nulls('[{f1:1,f2:null},2,null,3]')/literal/entry entryliteral[{f1:1},2,null,3]/literal/entry /row + row + entryparaliteraljsonb_replace(target jsonb, path text[], replacement jsonb)/literal + /para/entry + entryparatypejsonb/type/para/entry + entry + Returns replaceabletarget/replaceable + with the section designated by replaceablepath/replaceable + replaced by replaceablereplacement/replaceable. + /entry + entryliteraljsonb_replace('[{f1:1,f2:null},2,null,3]', '{0,f1}','[2,3,4]')/literal/entry + entryliteral[{f1:[2,3,4],f2:null},2,null,3]/literal +/entry + /row + row + entryparaliteraljsonb_pretty(from_json jsonb)/literal + /para/entry + entryparatypetext/type/para/entry + entry + Returns replaceablefrom_json/replaceable + as indented json text. + /entry + entryliteraljsonb_pretty('[{f1:1,f2:null},2,null,3]')/literal/entry + entry +programlisting + [ + { + f1: 1, +
Re: [HACKERS] mogrify and indent features for jsonb
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
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
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
On Sat, 14 Feb 2015 22:06:07 -0500 Andrew Dunstan and...@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 -- 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
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
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 entryDo all of these key/element emphasisstrings/emphasis exist?/entry entryliteral'[a, b]'::jsonb ?amp; array['a', 'b']/literal/entry /row + row +entryliteral||/literal/entry +entrytypejsonb/type/entry +entryConcatentate these two values to make a new value/entry +entryliteral'[a, b]'::jsonb || '[c, d]'::jsonb/literal/entry + /row + row +entryliteral-/literal/entry +entrytypetext/type/entry +entryDelete the field with this key, or element with this value/entry +entryliteral'{a: b}'::jsonb - 'a' /literal/entry + /row + row +entryliteral-/literal/entry +entrytypeinteger/type/entry +entryDelete the field or element with this index (Negative integers count from the end)/entry +entryliteral'[a, b]'::jsonb - 1 /literal/entry + /row + row +entryliteral-/literal/entry +entrytypetext[]/type/entry +entryDelete the field or element with this path/entry +entryliteral'[a, {b:1}]'::jsonb - '{1,b}'::text[] /literal/entry + /row /tbody /tgroup /table @@ -10766,6 +10790,42 @@ table2-mapping entryliteraljson_strip_nulls('[{f1:1,f2:null},2,null,3]')/literal/entry entryliteral[{f1:1},2,null,3]/literal/entry /row + row + entryparaliteraljsonb_replace(target jsonb, path text[], replacement jsonb)/literal + /para/entry + entryparatypejsonb/type/para/entry + entry + Returns replaceabletarget/replaceable + with the section designated by replaceablepath/replaceable + replaced by replaceablereplacement/replaceable. + /entry + entryliteraljsonb_replace('[{f1:1,f2:null},2,null,3]', '{0,f1}','[2,3,4]')/literal/entry + entryliteral[{f1:[2,3,4],f2:null},2,null,3]/literal +/entry + /row + row + entryparaliteraljsonb_indent(from_json jsonb)/literal + /para/entry + entryparatypetext/type/para/entry + entry + Returns replaceablefrom_json/replaceable + as indented json text. + /entry + entryliteraljsonb_indent('[{f1:1,f2:null},2,null,3]')/literal/entry + entry +programlisting + [ + { + f1: 1, + f2: null + }, + 2, + null, + 3 + ] +/programlisting +/entry + /row /tbody /tgroup /table 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
Re: [HACKERS] mogrify and indent features for jsonb
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
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 j...@agliodbs.com 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
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
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
On 24 February 2015 at 19:16, Josh Berkus j...@agliodbs.com 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
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
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 p...@2ndquadrant.com 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
On 15 February 2015 at 03:06, Andrew Dunstan and...@dunslane.net 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
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
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
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
Re: [HACKERS] mogrify and indent features for jsonb
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 entryDo all of these key/element emphasisstrings/emphasis exist?/entry entryliteral'[a, b]'::jsonb ?amp; array['a', 'b']/literal/entry /row + row +entryliteral||/literal/entry +entrytypejsonb/type/entry +entryConcatentate these two values to make a new value/entry +entryliteral'[a, b]'::jsonb || '[c, d]'::jsonb/literal/entry + /row + row +entryliteral-/literal/entry +entrytypetext/type/entry +entryDelete the field with this key, or element with this value/entry +entryliteral'{a: b}'::jsonb - 'a' /literal/entry + /row + row +entryliteral-/literal/entry +entrytypeinteger/type/entry +entryDelete the field or element with this index (Negative integers count from the end)/entry +entryliteral'[a, b]'::jsonb - 1 /literal/entry + /row + row +entryliteral-/literal/entry +entrytypetext[]/type/entry +entryDelete the field or element with this path/entry +entryliteral'[a, {b:1}]'::jsonb - '{1,b}'::text[] /literal/entry + /row /tbody /tgroup /table @@ -10760,6 +10784,42 @@ table2-mapping entryliteraljson_strip_nulls('[{f1:1,f2:null},2,null,3]')/literal/entry entryliteral[{f1:1},2,null,3]/literal/entry /row + row + entryparaliteraljsonb_replace(target jsonb, path text[], replacement jsonb)/literal + /para/entry + entryparatypejsonb/type/para/entry + entry + Returns replaceabletarget/replaceable + with the section designated by replaceablepath/replaceable + replaced by replaceablereplacement/replaceable. + /entry + entryliteraljsonb_replace('[{f1:1,f2:null},2,null,3]', '{0,f1},'[2,3,4]')/literal/entry + entryliteral[{f1:[2,3,4],f2:null},2,null,3]/literal +/entry + /row + row + entryparaliteraljsonb_indent(from_json jsonb)/literal + /para/entry + entryparatypetext/type/para/entry + entry + Returns replaceablefrom_json/replaceable + as indented json text. + /entry + entryliteraljsonb_indent('[{f1:1,f2:null},2,null,3]')/literal/entry + entry +programlisting + [ + { + f1: 1, + f2: null + }, + 2, + null, + 3 + ] +/programlisting +/entry + /row /tbody /tgroup /table 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