Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commitc694701

Browse files
committed
Additional functions and operators for jsonb
jsonb_pretty(jsonb) produces nicely indented json output.jsonb || jsonb concatenates two jsonb values.jsonb - text removes a key and its associated value from the jsonjsonb - int removes the designated array elementjsonb - text[] removes a key and associated value or array element atthe designated pathjsonb_replace(jsonb,text[],jsonb) replaces the array element designatedby the path or the value associated with the key designated by the pathwith the given value.Original work by Dmitry Dolgov, adapted and reworked for PostgreSQL coreby Andrew Dunstan, reviewed and tidied up by Petr Jelinek.
1 parentafb9249 commitc694701

File tree

9 files changed

+1813
-16
lines changed

9 files changed

+1813
-16
lines changed

‎doc/src/sgml/func.sgml

Lines changed: 62 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -10293,6 +10293,32 @@ table2-mapping
1029310293
<entry>Do all of these key/element <emphasis>strings</emphasis> exist?</entry>
1029410294
<entry><literal>'["a", "b"]'::jsonb ?&amp; array['a', 'b']</literal></entry>
1029510295
</row>
10296+
<row>
10297+
<entry><literal>||</literal></entry>
10298+
<entry><type>jsonb</type></entry>
10299+
<entry>Concatentate two jsonb values into a new jsonb value</entry>
10300+
<entry><literal>'["a", "b"]'::jsonb || '["c", "d"]'::jsonb</literal></entry>
10301+
</row>
10302+
<row>
10303+
<entry><literal>-</literal></entry>
10304+
<entry><type>text</type></entry>
10305+
<entry>Delete the field with a specified key, or element with this
10306+
value</entry>
10307+
<entry><literal>'{"a": "b"}'::jsonb - 'a' </literal></entry>
10308+
</row>
10309+
<row>
10310+
<entry><literal>-</literal></entry>
10311+
<entry><type>integer</type></entry>
10312+
<entry>Delete the field or element with specified index (Negative
10313+
integers count from the end)</entry>
10314+
<entry><literal>'["a", "b"]'::jsonb - 1 </literal></entry>
10315+
</row>
10316+
<row>
10317+
<entry><literal>-</literal></entry>
10318+
<entry><type>text[]</type></entry>
10319+
<entry>Delete the field or element with specified path</entry>
10320+
<entry><literal>'["a", {"b":1}]'::jsonb - '{1,b}'::text[] </literal></entry>
10321+
</row>
1029610322
</tbody>
1029710323
</tgroup>
1029810324
</table>
@@ -10803,6 +10829,42 @@ table2-mapping
1080310829
<entry><literal>json_strip_nulls('[{"f1":1,"f2":null},2,null,3]')</literal></entry>
1080410830
<entry><literal>[{"f1":1},2,null,3]</literal></entry>
1080510831
</row>
10832+
<row>
10833+
<entry><para><literal>jsonb_replace(target jsonb, path text[], replacement jsonb)</literal>
10834+
</para></entry>
10835+
<entry><para><type>jsonb</type></para></entry>
10836+
<entry>
10837+
Returns <replaceable>target</replaceable>
10838+
with the section designated by <replaceable>path</replaceable>
10839+
replaced by <replaceable>replacement</replaceable>.
10840+
</entry>
10841+
<entry><literal>jsonb_replace('[{"f1":1,"f2":null},2,null,3]', '{0,f1}','[2,3,4]')</literal></entry>
10842+
<entry><literal>[{"f1":[2,3,4],"f2":null},2,null,3]</literal>
10843+
</entry>
10844+
</row>
10845+
<row>
10846+
<entry><para><literal>jsonb_pretty(from_json jsonb)</literal>
10847+
</para></entry>
10848+
<entry><para><type>text</type></para></entry>
10849+
<entry>
10850+
Returns <replaceable>from_json</replaceable>
10851+
as indented json text.
10852+
</entry>
10853+
<entry><literal>jsonb_pretty('[{"f1":1,"f2":null},2,null,3]')</literal></entry>
10854+
<entry>
10855+
<programlisting>
10856+
[
10857+
{
10858+
"f1": 1,
10859+
"f2": null
10860+
},
10861+
2,
10862+
null,
10863+
3
10864+
]
10865+
</programlisting>
10866+
</entry>
10867+
</row>
1080610868
</tbody>
1080710869
</tgroup>
1080810870
</table>

‎src/backend/utils/adt/jsonb.c

Lines changed: 70 additions & 11 deletions
Original file line numberDiff line numberDiff line change
@@ -85,6 +85,8 @@ static void datum_to_jsonb(Datum val, bool is_null, JsonbInState *result,
8585
staticvoidadd_jsonb(Datumval,boolis_null,JsonbInState*result,
8686
Oidval_type,boolkey_scalar);
8787
staticJsonbParseState*clone_parse_state(JsonbParseState*state);
88+
staticchar*JsonbToCStringWorker(StringInfoout,JsonbContainer*in,intestimated_len,boolindent);
89+
staticvoidadd_indent(StringInfoout,boolindent,intlevel);
8890

8991
/*
9092
* jsonb type input function
@@ -421,13 +423,40 @@ jsonb_in_scalar(void *pstate, char *token, JsonTokenType tokentype)
421423
*/
422424
char*
423425
JsonbToCString(StringInfoout,JsonbContainer*in,intestimated_len)
426+
{
427+
returnJsonbToCStringWorker(out,in,estimated_len, false);
428+
}
429+
430+
/*
431+
* same thing but with indentation turned on
432+
*/
433+
char*
434+
JsonbToCStringIndent(StringInfoout,JsonbContainer*in,intestimated_len)
435+
{
436+
returnJsonbToCStringWorker(out,in,estimated_len, true);
437+
}
438+
439+
/*
440+
* common worker for above two functions
441+
*/
442+
staticchar*
443+
JsonbToCStringWorker(StringInfoout,JsonbContainer*in,intestimated_len,boolindent)
424444
{
425445
boolfirst= true;
426446
JsonbIterator*it;
427447
JsonbIteratorTokentype=WJB_DONE;
428448
JsonbValuev;
429449
intlevel=0;
430450
boolredo_switch= false;
451+
/* If we are indenting, don't add a space after a comma */
452+
intispaces=indent ?1 :2;
453+
/*
454+
* Don't indent the very first item. This gets set to the indent flag
455+
* at the bottom of the loop.
456+
*/
457+
booluse_indent= false;
458+
boolraw_scalar= false;
459+
boollast_was_key= false;
431460

432461
if (out==NULL)
433462
out=makeStringInfo();
@@ -444,26 +473,36 @@ JsonbToCString(StringInfo out, JsonbContainer *in, int estimated_len)
444473
{
445474
caseWJB_BEGIN_ARRAY:
446475
if (!first)
447-
appendBinaryStringInfo(out,", ",2);
448-
first= true;
476+
appendBinaryStringInfo(out,", ",ispaces);
449477

450478
if (!v.val.array.rawScalar)
451-
appendStringInfoChar(out,'[');
479+
{
480+
add_indent(out,use_indent&& !last_was_key,level);
481+
appendStringInfoCharMacro(out,'[');
482+
}
483+
else
484+
raw_scalar= true;
485+
486+
first= true;
452487
level++;
453488
break;
454489
caseWJB_BEGIN_OBJECT:
455490
if (!first)
456-
appendBinaryStringInfo(out,", ",2);
457-
first= true;
491+
appendBinaryStringInfo(out,", ",ispaces);
492+
493+
add_indent(out,use_indent&& !last_was_key,level);
458494
appendStringInfoCharMacro(out,'{');
459495

496+
first= true;
460497
level++;
461498
break;
462499
caseWJB_KEY:
463500
if (!first)
464-
appendBinaryStringInfo(out,", ",2);
501+
appendBinaryStringInfo(out,", ",ispaces);
465502
first= true;
466503

504+
add_indent(out,use_indent,level);
505+
467506
/* json rules guarantee this is a string */
468507
jsonb_put_escaped_value(out,&v);
469508
appendBinaryStringInfo(out,": ",2);
@@ -488,33 +527,53 @@ JsonbToCString(StringInfo out, JsonbContainer *in, int estimated_len)
488527
break;
489528
caseWJB_ELEM:
490529
if (!first)
491-
appendBinaryStringInfo(out,", ",2);
492-
else
493-
first= false;
530+
appendBinaryStringInfo(out,", ",ispaces);
531+
first= false;
494532

533+
if (!raw_scalar)
534+
add_indent(out,use_indent,level);
495535
jsonb_put_escaped_value(out,&v);
496536
break;
497537
caseWJB_END_ARRAY:
498538
level--;
499-
if (!v.val.array.rawScalar)
500-
appendStringInfoChar(out,']');
539+
if (!raw_scalar)
540+
{
541+
add_indent(out,use_indent,level);
542+
appendStringInfoCharMacro(out,']');
543+
}
501544
first= false;
502545
break;
503546
caseWJB_END_OBJECT:
504547
level--;
548+
add_indent(out,use_indent,level);
505549
appendStringInfoCharMacro(out,'}');
506550
first= false;
507551
break;
508552
default:
509553
elog(ERROR,"unknown jsonb iterator token type");
510554
}
555+
use_indent=indent;
556+
last_was_key=redo_switch;
511557
}
512558

513559
Assert(level==0);
514560

515561
returnout->data;
516562
}
517563

564+
staticvoid
565+
add_indent(StringInfoout,boolindent,intlevel)
566+
{
567+
if (indent)
568+
{
569+
inti;
570+
571+
appendStringInfoCharMacro(out,'\n');
572+
for (i=0;i<level;i++)
573+
appendBinaryStringInfo(out," ",4);
574+
}
575+
}
576+
518577

519578
/*
520579
* Determine how we want to render values of a given type in datum_to_jsonb.

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp