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

Commit95d737f

Browse files
committed
Add 'ignore_nulls' option to row_to_json
Provide an option to skip NULL values in a row when generating a JSONobject from that row with row_to_json. This can reduce the size of theJSON object in cases where columns are NULL without really reducing theinformation in the JSON object.This also makes row_to_json into a single function with default values,rather than having multiple functions. In passing, change array_to_jsonto also be a single function with default values (we don't add an'ignore_nulls' option yet- it's not clear that there is a sensibleuse-case there, and it hasn't been asked for in any case).Pavel Stehule
1 parentc3c75fc commit95d737f

File tree

8 files changed

+118
-51
lines changed

8 files changed

+118
-51
lines changed

‎doc/src/sgml/func.sgml‎

Lines changed: 4 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -10309,11 +10309,13 @@ table2-mapping
1030910309
</row>
1031010310
<row>
1031110311
<entry>
10312-
<literal>row_to_json(record [,pretty_bool])</literal>
10312+
<literal>row_to_json(rowvalrecord [,pretty bool [, ignore_nulls bool]])</literal>
1031310313
</entry>
1031410314
<entry>
1031510315
Returns the row as a JSON object. Line feeds will be added between
10316-
level-1 elements if <parameter>pretty_bool</parameter> is true.
10316+
level-1 elements if <parameter>pretty_bool</parameter> is true. Elements
10317+
with NULL values will be skipped when <parameter>ignore_nulls</parameter>
10318+
is true.
1031710319
</entry>
1031810320
<entry><literal>row_to_json(row(1,'foo'))</literal></entry>
1031910321
<entry><literal>{"f1":1,"f2":"foo"}</literal></entry>

‎src/backend/catalog/system_views.sql‎

Lines changed: 14 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -867,3 +867,17 @@ RETURNS interval
867867
LANGUAGE INTERNAL
868868
STRICT IMMUTABLE
869869
AS'make_interval';
870+
871+
CREATEOR REPLACE FUNCTION
872+
row_to_json(rowval record, prettyboolean DEFAULT false, ignore_nullsboolean DEFAULT false)
873+
RETURNS json
874+
LANGUAGE INTERNAL
875+
STRICT STABLE
876+
AS'row_to_json';
877+
878+
CREATEOR REPLACE FUNCTION
879+
array_to_json(arrayval anyarray, prettyboolean DEFAULT false)
880+
RETURNS json
881+
LANGUAGE INTERNAL
882+
STRICT STABLE
883+
AS'array_to_json';

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

Lines changed: 15 additions & 40 deletions
Original file line numberDiff line numberDiff line change
@@ -79,7 +79,8 @@ static void report_invalid_token(JsonLexContext *lex);
7979
staticintreport_json_context(JsonLexContext*lex);
8080
staticchar*extract_mb_char(char*s);
8181
staticvoidcomposite_to_json(Datumcomposite,StringInforesult,
82-
booluse_line_feeds);
82+
booluse_line_feeds,
83+
boolignore_nulls);
8384
staticvoidarray_dim_to_json(StringInforesult,intdim,intndims,int*dims,
8485
Datum*vals,bool*nulls,int*valcount,
8586
JsonTypeCategorytcategory,Oidoutfuncoid,
@@ -1362,7 +1363,7 @@ datum_to_json(Datum val, bool is_null, StringInfo result,
13621363
array_to_json_internal(val,result, false);
13631364
break;
13641365
caseJSONTYPE_COMPOSITE:
1365-
composite_to_json(val,result, false);
1366+
composite_to_json(val,result, false, false);
13661367
break;
13671368
caseJSONTYPE_BOOL:
13681369
outputstr=DatumGetBool(val) ?"true" :"false";
@@ -1591,7 +1592,8 @@ array_to_json_internal(Datum array, StringInfo result, bool use_line_feeds)
15911592
* Turn a composite / record into JSON.
15921593
*/
15931594
staticvoid
1594-
composite_to_json(Datumcomposite,StringInforesult,booluse_line_feeds)
1595+
composite_to_json(Datumcomposite,StringInforesult,booluse_line_feeds,
1596+
boolignore_nulls)
15951597
{
15961598
HeapTupleHeadertd;
15971599
OidtupType;
@@ -1630,6 +1632,12 @@ composite_to_json(Datum composite, StringInfo result, bool use_line_feeds)
16301632
if (tupdesc->attrs[i]->attisdropped)
16311633
continue;
16321634

1635+
val=heap_getattr(tuple,i+1,tupdesc,&isnull);
1636+
1637+
/* Don't serialize NULL field when we don't want it */
1638+
if (isnull&&ignore_nulls)
1639+
continue;
1640+
16331641
if (needsep)
16341642
appendStringInfoString(result,sep);
16351643
needsep= true;
@@ -1638,8 +1646,6 @@ composite_to_json(Datum composite, StringInfo result, bool use_line_feeds)
16381646
escape_json(result,attname);
16391647
appendStringInfoChar(result,':');
16401648

1641-
val=heap_getattr(tuple,i+1,tupdesc,&isnull);
1642-
16431649
if (isnull)
16441650
{
16451651
tcategory=JSONTYPE_NULL;
@@ -1687,27 +1693,11 @@ add_json(Datum val, bool is_null, StringInfo result,
16871693
datum_to_json(val,is_null,result,tcategory,outfuncoid,key_scalar);
16881694
}
16891695

1690-
/*
1691-
* SQL function array_to_json(row)
1692-
*/
1693-
externDatum
1694-
array_to_json(PG_FUNCTION_ARGS)
1695-
{
1696-
Datumarray=PG_GETARG_DATUM(0);
1697-
StringInforesult;
1698-
1699-
result=makeStringInfo();
1700-
1701-
array_to_json_internal(array,result, false);
1702-
1703-
PG_RETURN_TEXT_P(cstring_to_text_with_len(result->data,result->len));
1704-
}
1705-
17061696
/*
17071697
* SQL function array_to_json(row, prettybool)
17081698
*/
17091699
externDatum
1710-
array_to_json_pretty(PG_FUNCTION_ARGS)
1700+
array_to_json(PG_FUNCTION_ARGS)
17111701
{
17121702
Datumarray=PG_GETARG_DATUM(0);
17131703
booluse_line_feeds=PG_GETARG_BOOL(1);
@@ -1721,34 +1711,19 @@ array_to_json_pretty(PG_FUNCTION_ARGS)
17211711
}
17221712

17231713
/*
1724-
* SQL function row_to_json(row)
1714+
* SQL function row_to_json(rowval record, pretty bool, ignore_nulls bool)
17251715
*/
17261716
externDatum
17271717
row_to_json(PG_FUNCTION_ARGS)
1728-
{
1729-
Datumarray=PG_GETARG_DATUM(0);
1730-
StringInforesult;
1731-
1732-
result=makeStringInfo();
1733-
1734-
composite_to_json(array,result, false);
1735-
1736-
PG_RETURN_TEXT_P(cstring_to_text_with_len(result->data,result->len));
1737-
}
1738-
1739-
/*
1740-
* SQL function row_to_json(row, prettybool)
1741-
*/
1742-
externDatum
1743-
row_to_json_pretty(PG_FUNCTION_ARGS)
17441718
{
17451719
Datumarray=PG_GETARG_DATUM(0);
17461720
booluse_line_feeds=PG_GETARG_BOOL(1);
1721+
boolignore_nulls=PG_GETARG_BOOL(2);
17471722
StringInforesult;
17481723

17491724
result=makeStringInfo();
17501725

1751-
composite_to_json(array,result,use_line_feeds);
1726+
composite_to_json(array,result,use_line_feeds,ignore_nulls);
17521727

17531728
PG_RETURN_TEXT_P(cstring_to_text_with_len(result->data,result->len));
17541729
}

‎src/include/catalog/catversion.h‎

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -53,6 +53,6 @@
5353
*/
5454

5555
/*yyyymmddN */
56-
#defineCATALOG_VERSION_NO201409101
56+
#defineCATALOG_VERSION_NO201409111
5757

5858
#endif

‎src/include/catalog/pg_proc.h‎

Lines changed: 2 additions & 6 deletions
Original file line numberDiff line numberDiff line change
@@ -4203,14 +4203,10 @@ DATA(insert OID = 323 ( json_recv PGNSP PGUID 12 1 0 0 0 f f f f t f i 1 0
42034203
DESCR("I/O");
42044204
DATA(insertOID=324 (json_sendPGNSPPGUID121000fffftfi1017"114"_null__null__null__null_json_send_null__null__null_ ));
42054205
DESCR("I/O");
4206-
DATA(insertOID=3153 (array_to_jsonPGNSPPGUID121000fffftfs10114"2277"_null__null__null__null_array_to_json_null__null__null_ ));
4206+
DATA(insertOID=3153 (array_to_jsonPGNSPPGUID121000fffftfs20114"2277 16"_null__null_"{arrayval,pretty}"_null_array_to_json_null__null__null_ ));
42074207
DESCR("map array to json");
4208-
DATA(insertOID=3154 (array_to_jsonPGNSPPGUID121000fffftfs20114"2277 16"_null__null__null__null_array_to_json_pretty_null__null__null_ ));
4209-
DESCR("map array to json with optional pretty printing");
4210-
DATA(insertOID=3155 (row_to_jsonPGNSPPGUID121000fffftfs10114"2249"_null__null__null__null_row_to_json_null__null__null_ ));
4208+
DATA(insertOID=3155 (row_to_jsonPGNSPPGUID121000fffftfs30114"2249 16 16"_null__null_"{rowval,pretty,ignore_nulls}"_null_row_to_json_null__null__null_ ));
42114209
DESCR("map row to json");
4212-
DATA(insertOID=3156 (row_to_jsonPGNSPPGUID121000fffftfs20114"2249 16"_null__null__null__null_row_to_json_pretty_null__null__null_ ));
4213-
DESCR("map row to json with optional pretty printing");
42144210
DATA(insertOID=3173 (json_agg_transfnPGNSPPGUID121000ffffffi202281"2281 2283"_null__null__null__null_json_agg_transfn_null__null__null_ ));
42154211
DESCR("json aggregate transition function");
42164212
DATA(insertOID=3174 (json_agg_finalfnPGNSPPGUID121000ffffffi10114"2281"_null__null__null__null_json_agg_finalfn_null__null__null_ ));

‎src/include/utils/json.h‎

Lines changed: 0 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -23,9 +23,7 @@ extern Datum json_out(PG_FUNCTION_ARGS);
2323
externDatumjson_recv(PG_FUNCTION_ARGS);
2424
externDatumjson_send(PG_FUNCTION_ARGS);
2525
externDatumarray_to_json(PG_FUNCTION_ARGS);
26-
externDatumarray_to_json_pretty(PG_FUNCTION_ARGS);
2726
externDatumrow_to_json(PG_FUNCTION_ARGS);
28-
externDatumrow_to_json_pretty(PG_FUNCTION_ARGS);
2927
externDatumto_json(PG_FUNCTION_ARGS);
3028

3129
externDatumjson_agg_transfn(PG_FUNCTION_ARGS);

‎src/test/regress/expected/json.out‎

Lines changed: 58 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -397,12 +397,70 @@ FROM rows q;
397397
"y":"txt3"}
398398
(3 rows)
399399

400+
SELECT row_to_json(q,pretty := true)
401+
FROM rows q;
402+
row_to_json
403+
--------------
404+
{"x":1, +
405+
"y":"txt1"}
406+
{"x":2, +
407+
"y":"txt2"}
408+
{"x":3, +
409+
"y":"txt3"}
410+
(3 rows)
411+
400412
SELECT row_to_json(row((select array_agg(x) as d from generate_series(5,10) x)),false);
401413
row_to_json
402414
-----------------------
403415
{"f1":[5,6,7,8,9,10]}
404416
(1 row)
405417

418+
WITH x AS (SELECT a,b,c FROM (VALUES(10,20,30),
419+
(10,NULL, NULL),
420+
(NULL, NULL, NULL)) g(a,b,c))
421+
SELECT row_to_json(x, false, false) FROM x;
422+
row_to_json
423+
------------------------------
424+
{"a":10,"b":20,"c":30}
425+
{"a":10,"b":null,"c":null}
426+
{"a":null,"b":null,"c":null}
427+
(3 rows)
428+
429+
WITH x AS (SELECT a,b,c FROM (VALUES(10,20,30),
430+
(10,NULL, NULL),
431+
(NULL, NULL, NULL)) g(a,b,c))
432+
SELECT row_to_json(x, false, true) FROM x;
433+
row_to_json
434+
------------------------
435+
{"a":10,"b":20,"c":30}
436+
{"a":10}
437+
{}
438+
(3 rows)
439+
440+
WITH x AS (SELECT a,b,c FROM (VALUES(10,20,30),
441+
(10,NULL, NULL),
442+
(NULL, NULL, NULL)) g(a,b,c))
443+
SELECT row_to_json(x, ignore_nulls := true) FROM x;
444+
row_to_json
445+
------------------------
446+
{"a":10,"b":20,"c":30}
447+
{"a":10}
448+
{}
449+
(3 rows)
450+
451+
WITH x AS (SELECT a,b,c FROM (VALUES(10,20,30),
452+
(10,NULL, NULL),
453+
(NULL, NULL, NULL)) g(a,b,c))
454+
SELECT row_to_json(x, ignore_nulls := true, pretty := true) FROM x;
455+
row_to_json
456+
-------------
457+
{"a":10, +
458+
"b":20, +
459+
"c":30}
460+
{"a":10}
461+
{}
462+
(3 rows)
463+
406464
-- to_json, timestamps
407465
select to_json(timestamp '2014-05-28 12:22:35.614298');
408466
to_json

‎src/test/regress/sql/json.sql‎

Lines changed: 24 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -98,8 +98,32 @@ FROM generate_series(1,3) AS x;
9898
SELECT row_to_json(q,true)
9999
FROM rows q;
100100

101+
SELECT row_to_json(q,pretty := true)
102+
FROM rows q;
103+
101104
SELECT row_to_json(row((select array_agg(x)as dfrom generate_series(5,10) x)),false);
102105

106+
WITH xAS (SELECT a,b,cFROM (VALUES(10,20,30),
107+
(10,NULL,NULL),
108+
(NULL,NULL,NULL)) g(a,b,c))
109+
SELECT row_to_json(x, false, false)FROM x;
110+
111+
WITH xAS (SELECT a,b,cFROM (VALUES(10,20,30),
112+
(10,NULL,NULL),
113+
(NULL,NULL,NULL)) g(a,b,c))
114+
SELECT row_to_json(x, false, true)FROM x;
115+
116+
WITH xAS (SELECT a,b,cFROM (VALUES(10,20,30),
117+
(10,NULL,NULL),
118+
(NULL,NULL,NULL)) g(a,b,c))
119+
SELECT row_to_json(x, ignore_nulls := true)FROM x;
120+
121+
WITH xAS (SELECT a,b,cFROM (VALUES(10,20,30),
122+
(10,NULL,NULL),
123+
(NULL,NULL,NULL)) g(a,b,c))
124+
SELECT row_to_json(x, ignore_nulls := true, pretty := true)FROM x;
125+
126+
103127
-- to_json, timestamps
104128

105129
select to_json(timestamp'2014-05-28 12:22:35.614298');

0 commit comments

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