Skip to content

Commit 7e354ab

Browse files
adunstanadunstan
committed
Add several generator functions for jsonb that exist for json.
The functions are: to_jsonb() jsonb_object() jsonb_build_object() jsonb_build_array() jsonb_agg() jsonb_object_agg() Also along the way some better logic is implemented in json_categorize_type() to match that in the newly implemented jsonb_categorize_type(). Andrew Dunstan, reviewed by Pavel Stehule and Alvaro Herrera.
1 parent 8ec8760 commit 7e354ab

File tree

11 files changed

+2003
-45
lines changed

11 files changed

+2003
-45
lines changed

‎doc/src/sgml/func.sgml

Lines changed: 72 additions & 25 deletions
Original file line numberDiff line numberDiff line change
@@ -10245,9 +10245,10 @@ table2-mapping
1024510245

1024610246
<para>
1024710247
<xref linkend="functions-json-creation-table"> shows the functions that are
10248-
available for creating <type>json</type> values.
10249-
(Currently, there are no equivalent functions for <type>jsonb</>, but you
10250-
can cast the result of one of these functions to <type>jsonb</>.)
10248+
available for creating <type>json</type> and <type>jsonb</type> values.
10249+
(There are no equivalent functions for <type>jsonb</>, of the <literal>row_to_json</>
10250+
and <literal>array_to_json</> functions. However, the <literal>to_jsonb</>
10251+
function supplies much the same functionality as these functions would.)
1025110252
</para>
1025210253

1025310254
<indexterm>
@@ -10268,6 +10269,18 @@ table2-mapping
1026810269
<indexterm>
1026910270
<primary>json_object</primary>
1027010271
</indexterm>
10272+
<indexterm>
10273+
<primary>to_jsonb</primary>
10274+
</indexterm>
10275+
<indexterm>
10276+
<primary>jsonb_build_array</primary>
10277+
</indexterm>
10278+
<indexterm>
10279+
<primary>jsonb_build_object</primary>
10280+
</indexterm>
10281+
<indexterm>
10282+
<primary>jsonb_object</primary>
10283+
</indexterm>
1027110284

1027210285
<table id="functions-json-creation-table">
1027310286
<title>JSON Creation Functions</title>
@@ -10282,17 +10295,18 @@ table2-mapping
1028210295
</thead>
1028310296
<tbody>
1028410297
<row>
10298+
<entry><para><literal>to_json(anyelement)</literal>
10299+
</para><para><literal>to_jsonb(anyelement)</literal>
10300+
</para></entry>
1028510301
<entry>
10286-
<literal>to_json(anyelement)</literal>
10287-
</entry>
10288-
<entry>
10289-
Returns the value as JSON. Arrays and composites are converted
10302+
Returns the value as <type>json</> or <type>jsonb</>.
10303+
Arrays and composites are converted
1029010304
(recursively) to arrays and objects; otherwise, if there is a cast
1029110305
from the type to <type>json</type>, the cast function will be used to
10292-
perform the conversion; otherwise, a JSON scalar value is produced.
10306+
perform the conversion; otherwise, a scalar value is produced.
1029310307
For any scalar type other than a number, a Boolean, or a null value,
10294-
the text representation will be used, properly quoted and escaped
10295-
so that it is a valid JSON string.
10308+
the text representation will be used, in such a fashion that it is a
10309+
valid <type>json</> or <type>jsonb</> value.
1029610310
</entry>
1029710311
<entry><literal>to_json('Fred said "Hi."'::text)</literal></entry>
1029810312
<entry><literal>"Fred said \"Hi.\""</literal></entry>
@@ -10321,9 +10335,9 @@ table2-mapping
1032110335
<entry><literal>{"f1":1,"f2":"foo"}</literal></entry>
1032210336
</row>
1032310337
<row>
10324-
<entry>
10325-
<literal>json_build_array(VARIADIC "any")</literal>
10326-
</entry>
10338+
<entry><para><literal>json_build_array(VARIADIC "any")</literal>
10339+
</para><para><literal>jsonb_build_array(VARIADIC "any")</literal>
10340+
</para></entry>
1032710341
<entry>
1032810342
Builds a possibly-heterogeneously-typed JSON array out of a variadic
1032910343
argument list.
@@ -10332,9 +10346,9 @@ table2-mapping
1033210346
<entry><literal>[1, 2, "3", 4, 5]</literal></entry>
1033310347
</row>
1033410348
<row>
10335-
<entry>
10336-
<literal>json_build_object(VARIADIC "any")</literal>
10337-
</entry>
10349+
<entry><para><literal>json_build_object(VARIADIC "any")</literal>
10350+
</para><para><literal>jsonb_build_object(VARIADIC "any")</literal>
10351+
</para></entry>
1033810352
<entry>
1033910353
Builds a JSON object out of a variadic argument list. By
1034010354
convention, the argument list consists of alternating
@@ -10344,9 +10358,9 @@ table2-mapping
1034410358
<entry><literal>{"foo": 1, "bar": 2}</literal></entry>
1034510359
</row>
1034610360
<row>
10347-
<entry>
10348-
<literal>json_object(text[])</literal>
10349-
</entry>
10361+
<entry><para><literal>json_object(text[])</literal>
10362+
</para><para><literal>jsonb_object(text[])</literal>
10363+
</para></entry>
1035010364
<entry>
1035110365
Builds a JSON object out of a text array. The array must have either
1035210366
exactly one dimension with an even number of members, in which case
@@ -10359,9 +10373,9 @@ table2-mapping
1035910373
<entry><literal>{"a": "1", "b": "def", "c": "3.5"}</literal></entry>
1036010374
</row>
1036110375
<row>
10362-
<entry>
10363-
<literal>json_object(keys text[], values text[])</literal>
10364-
</entry>
10376+
<entry><para><literal>json_object(keys text[], values text[])</literal>
10377+
</para><para><literal>json_object(keys text[], values text[])</literal>
10378+
</para></entry>
1036510379
<entry>
1036610380
This form of <function>json_object</> takes keys and values pairwise from two separate
1036710381
arrays. In all other respects it is identical to the one-argument form.
@@ -10780,7 +10794,8 @@ table2-mapping
1078010794
function <function>json_agg</function> which aggregates record
1078110795
values as JSON, and the aggregate function
1078210796
<function>json_object_agg</function> which aggregates pairs of values
10783-
into a JSON object.
10797+
into a JSON object, and their <type>jsonb</type> equivalents,
10798+
<function>jsonb_agg</> and <function>jsonb_object_agg</>.
1078410799
</para>
1078510800

1078610801
</sect1>
@@ -12224,6 +12239,22 @@ NULL baz</literallayout>(3 rows)</entry>
1222412239
<entry>aggregates records as a JSON array of objects</entry>
1222512240
</row>
1222612241

12242+
<row>
12243+
<entry>
12244+
<indexterm>
12245+
<primary>jsonb_agg</primary>
12246+
</indexterm>
12247+
<function>jsonb_agg(<replaceable class="parameter">record</replaceable>)</function>
12248+
</entry>
12249+
<entry>
12250+
<type>record</type>
12251+
</entry>
12252+
<entry>
12253+
<type>jsonb</type>
12254+
</entry>
12255+
<entry>aggregates records as a JSON array of objects</entry>
12256+
</row>
12257+
1222712258
<row>
1222812259
<entry>
1222912260
<indexterm>
@@ -12240,6 +12271,22 @@ NULL baz</literallayout>(3 rows)</entry>
1224012271
<entry>aggregates name/value pairs as a JSON object</entry>
1224112272
</row>
1224212273

12274+
<row>
12275+
<entry>
12276+
<indexterm>
12277+
<primary>jsonb_object_agg</primary>
12278+
</indexterm>
12279+
<function>jsonb_object_agg(<replaceable class="parameter">name</replaceable>, <replaceable class="parameter">value</replaceable>)</function>
12280+
</entry>
12281+
<entry>
12282+
<type>("any", "any")</type>
12283+
</entry>
12284+
<entry>
12285+
<type>jsonb</type>
12286+
</entry>
12287+
<entry>aggregates name/value pairs as a JSON object</entry>
12288+
</row>
12289+
1224312290
<row>
1224412291
<entry>
1224512292
<indexterm>
@@ -12386,8 +12433,8 @@ SELECT count(*) FROM sometable;
1238612433

1238712434
<para>
1238812435
The aggregate functions <function>array_agg</function>,
12389-
<function>json_agg</function>,
12390-
<function>json_object_agg</function>,
12436+
<function>json_agg</function>, <function>jsonb_agg</function>,
12437+
<function>json_object_agg</function>, <function>jsonb_object_agg</function>,
1239112438
<function>string_agg</function>,
1239212439
and <function>xmlagg</function>, as well as similar user-defined
1239312440
aggregate functions, produce meaningfully different result values

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

Lines changed: 26 additions & 18 deletions
Original file line numberDiff line numberDiff line change
@@ -15,7 +15,6 @@
1515

1616
#include "access/htup_details.h"
1717
#include "access/transam.h"
18-
#include "catalog/pg_cast.h"
1918
#include "catalog/pg_type.h"
2019
#include "executor/spi.h"
2120
#include "lib/stringinfo.h"
@@ -1281,10 +1280,14 @@ json_categorize_type(Oid typoid,
12811280
/* Look through any domain */
12821281
typoid = getBaseType(typoid);
12831282

1284-
/* We'll usually need to return the type output function */
1285-
getTypeOutputInfo(typoid, outfuncoid, &typisvarlena);
1283+
*outfuncoid = InvalidOid;
1284+
1285+
/*
1286+
* We need to get the output function for everything except date and
1287+
* timestamp types, array and composite types, booleans,
1288+
* and non-builtin types where there's a cast to json.
1289+
*/
12861290

1287-
/* Check for known types */
12881291
switch (typoid)
12891292
{
12901293
case BOOLOID:
@@ -1297,6 +1300,7 @@ json_categorize_type(Oid typoid,
12971300
case FLOAT4OID:
12981301
case FLOAT8OID:
12991302
case NUMERICOID:
1303+
getTypeOutputInfo(typoid, outfuncoid, &typisvarlena);
13001304
*tcategory = JSONTYPE_NUMERIC;
13011305
break;
13021306

@@ -1314,6 +1318,7 @@ json_categorize_type(Oid typoid,
13141318

13151319
case JSONOID:
13161320
case JSONBOID:
1321+
getTypeOutputInfo(typoid, outfuncoid, &typisvarlena);
13171322
*tcategory = JSONTYPE_JSON;
13181323
break;
13191324

@@ -1330,23 +1335,26 @@ json_categorize_type(Oid typoid,
13301335
/* but let's look for a cast to json, if it's not built-in */
13311336
if (typoid >= FirstNormalObjectId)
13321337
{
1333-
HeapTuple tuple;
1338+
Oid castfunc;
1339+
CoercionPathType ctype;
13341340

1335-
tuple = SearchSysCache2(CASTSOURCETARGET,
1336-
ObjectIdGetDatum(typoid),
1337-
ObjectIdGetDatum(JSONOID));
1338-
if (HeapTupleIsValid(tuple))
1341+
ctype = find_coercion_pathway(JSONOID, typoid,
1342+
COERCION_EXPLICIT, &castfunc);
1343+
if (ctype == COERCION_PATH_FUNC && OidIsValid(castfunc))
13391344
{
1340-
Form_pg_cast castForm = (Form_pg_cast) GETSTRUCT(tuple);
1341-
1342-
if (castForm->castmethod == COERCION_METHOD_FUNCTION)
1343-
{
1344-
*tcategory = JSONTYPE_CAST;
1345-
*outfuncoid = castForm->castfunc;
1346-
}
1347-
1348-
ReleaseSysCache(tuple);
1345+
*tcategory = JSONTYPE_CAST;
1346+
*outfuncoid = castfunc;
13491347
}
1348+
else
1349+
{
1350+
/* non builtin type with no cast */
1351+
getTypeOutputInfo(typoid, outfuncoid, &typisvarlena);
1352+
}
1353+
}
1354+
else
1355+
{
1356+
/* any other builtin type */
1357+
getTypeOutputInfo(typoid, outfuncoid, &typisvarlena);
13501358
}
13511359
}
13521360
break;

0 commit comments

Comments
 (0)