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

Commit38fb4d9

Browse files
committed
JSON generation improvements.
This adds the following: json_agg(anyrecord) -> json to_json(any) -> json hstore_to_json(hstore) -> json (also used as a cast) hstore_to_json_loose(hstore) -> jsonThe last provides heuristic treatment of numbers and booleans.Also, in json generation, if any non-builtin type has a cast to json,that function is used instead of the type's output function.Andrew Dunstan, reviewed by Steve Singer.Catalog version bumped.
1 parentdd28c41 commit38fb4d9

File tree

13 files changed

+699
-21
lines changed

13 files changed

+699
-21
lines changed

‎contrib/hstore/expected/hstore.out

Lines changed: 36 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1453,3 +1453,39 @@ select count(*) from testhstore where h = 'pos=>98, line=>371, node=>CBA, indexe
14531453
1
14541454
(1 row)
14551455

1456+
-- json
1457+
select hstore_to_json('"a key" =>1, b => t, c => null, d=> 12345, e => 012345, f=> 1.234, g=> 2.345e+4');
1458+
hstore_to_json
1459+
-------------------------------------------------------------------------------------------------
1460+
{"b": "t", "c": null, "d": "12345", "e": "012345", "f": "1.234", "g": "2.345e+4", "a key": "1"}
1461+
(1 row)
1462+
1463+
select cast( hstore '"a key" =>1, b => t, c => null, d=> 12345, e => 012345, f=> 1.234, g=> 2.345e+4' as json);
1464+
json
1465+
-------------------------------------------------------------------------------------------------
1466+
{"b": "t", "c": null, "d": "12345", "e": "012345", "f": "1.234", "g": "2.345e+4", "a key": "1"}
1467+
(1 row)
1468+
1469+
select hstore_to_json_loose('"a key" =>1, b => t, c => null, d=> 12345, e => 012345, f=> 1.234, g=> 2.345e+4');
1470+
hstore_to_json_loose
1471+
------------------------------------------------------------------------------------------
1472+
{"b": true, "c": null, "d": 12345, "e": "012345", "f": 1.234, "g": 2.345e+4, "a key": 1}
1473+
(1 row)
1474+
1475+
create table test_json_agg (f1 text, f2 hstore);
1476+
insert into test_json_agg values ('rec1','"a key" =>1, b => t, c => null, d=> 12345, e => 012345, f=> 1.234, g=> 2.345e+4'),
1477+
('rec2','"a key" =>2, b => f, c => "null", d=> -12345, e => 012345.6, f=> -1.234, g=> 0.345e-4');
1478+
select json_agg(q) from test_json_agg q;
1479+
json_agg
1480+
----------------------------------------------------------------------------------------------------------------------------
1481+
[{"f1":"rec1","f2":{"b": "t", "c": null, "d": "12345", "e": "012345", "f": "1.234", "g": "2.345e+4", "a key": "1"}}, +
1482+
{"f1":"rec2","f2":{"b": "f", "c": "null", "d": "-12345", "e": "012345.6", "f": "-1.234", "g": "0.345e-4", "a key": "2"}}]
1483+
(1 row)
1484+
1485+
select json_agg(q) from (select f1, hstore_to_json_loose(f2) as f2 from test_json_agg) q;
1486+
json_agg
1487+
----------------------------------------------------------------------------------------------------------------------
1488+
[{"f1":"rec1","f2":{"b": true, "c": null, "d": 12345, "e": "012345", "f": 1.234, "g": 2.345e+4, "a key": 1}}, +
1489+
{"f1":"rec2","f2":{"b": false, "c": "null", "d": -12345, "e": "012345.6", "f": -1.234, "g": 0.345e-4, "a key": 2}}]
1490+
(1 row)
1491+

‎contrib/hstore/hstore--1.1.sql

Lines changed: 13 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -234,6 +234,19 @@ LANGUAGE C IMMUTABLE STRICT;
234234
CREATE CAST (text[]AS hstore)
235235
WITH FUNCTION hstore(text[]);
236236

237+
CREATEFUNCTIONhstore_to_json(hstore)
238+
RETURNS json
239+
AS'MODULE_PATHNAME','hstore_to_json'
240+
LANGUAGE C IMMUTABLE STRICT;
241+
242+
CREATE CAST (hstoreAS json)
243+
WITH FUNCTION hstore_to_json(hstore);
244+
245+
CREATEFUNCTIONhstore_to_json_loose(hstore)
246+
RETURNS json
247+
AS'MODULE_PATHNAME','hstore_to_json_loose'
248+
LANGUAGE C IMMUTABLE STRICT;
249+
237250
CREATEFUNCTIONhstore(record)
238251
RETURNS hstore
239252
AS'MODULE_PATHNAME','hstore_from_record'

‎contrib/hstore/hstore_io.c

Lines changed: 217 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -8,7 +8,10 @@
88
#include"access/htup_details.h"
99
#include"catalog/pg_type.h"
1010
#include"funcapi.h"
11+
#include"lib/stringinfo.h"
1112
#include"libpq/pqformat.h"
13+
#include"utils/builtins.h"
14+
#include"utils/json.h"
1215
#include"utils/lsyscache.h"
1316
#include"utils/typcache.h"
1417

@@ -1209,3 +1212,217 @@ hstore_send(PG_FUNCTION_ARGS)
12091212

12101213
PG_RETURN_BYTEA_P(pq_endtypsend(&buf));
12111214
}
1215+
1216+
1217+
/*
1218+
* hstore_to_json_loose
1219+
*
1220+
* This is a heuristic conversion to json which treats
1221+
* 't' and 'f' as booleans and strings that look like numbers as numbers,
1222+
* as long as they don't start with a leading zero followed by another digit
1223+
* (think zip codes or phone numbers starting with 0).
1224+
*/
1225+
PG_FUNCTION_INFO_V1(hstore_to_json_loose);
1226+
Datumhstore_to_json_loose(PG_FUNCTION_ARGS);
1227+
Datum
1228+
hstore_to_json_loose(PG_FUNCTION_ARGS)
1229+
{
1230+
HStore*in=PG_GETARG_HS(0);
1231+
intbuflen,
1232+
i;
1233+
intcount=HS_COUNT(in);
1234+
char*out,
1235+
*ptr;
1236+
char*base=STRPTR(in);
1237+
HEntry*entries=ARRPTR(in);
1238+
boolis_number;
1239+
StringInfosrc,
1240+
dst;
1241+
1242+
if (count==0)
1243+
{
1244+
out=palloc(1);
1245+
*out='\0';
1246+
PG_RETURN_TEXT_P(cstring_to_text(out));
1247+
}
1248+
1249+
buflen=3;
1250+
1251+
/*
1252+
* Formula adjusted slightly from the logic in hstore_out. We have to take
1253+
* account of out treatment of booleans to be a bit more pessimistic about
1254+
* the length of values.
1255+
*/
1256+
1257+
for (i=0;i<count;i++)
1258+
{
1259+
/* include "" and colon-space and comma-space */
1260+
buflen+=6+2*HS_KEYLEN(entries,i);
1261+
/* include "" only if nonnull */
1262+
buflen+=3+ (HS_VALISNULL(entries,i)
1263+
?1
1264+
:2*HS_VALLEN(entries,i));
1265+
}
1266+
1267+
out=ptr=palloc(buflen);
1268+
1269+
src=makeStringInfo();
1270+
dst=makeStringInfo();
1271+
1272+
*ptr++='{';
1273+
1274+
for (i=0;i<count;i++)
1275+
{
1276+
resetStringInfo(src);
1277+
resetStringInfo(dst);
1278+
appendBinaryStringInfo(src,HS_KEY(entries,base,i),HS_KEYLEN(entries,i));
1279+
escape_json(dst,src->data);
1280+
strncpy(ptr,dst->data,dst->len);
1281+
ptr+=dst->len;
1282+
*ptr++=':';
1283+
*ptr++=' ';
1284+
resetStringInfo(dst);
1285+
if (HS_VALISNULL(entries,i))
1286+
appendStringInfoString(dst,"null");
1287+
/* guess that values of 't' or 'f' are booleans */
1288+
elseif (HS_VALLEN(entries,i)==1&&*(HS_VAL(entries,base,i))=='t')
1289+
appendStringInfoString(dst,"true");
1290+
elseif (HS_VALLEN(entries,i)==1&&*(HS_VAL(entries,base,i))=='f')
1291+
appendStringInfoString(dst,"false");
1292+
else
1293+
{
1294+
is_number= false;
1295+
resetStringInfo(src);
1296+
appendBinaryStringInfo(src,HS_VAL(entries,base,i),HS_VALLEN(entries,i));
1297+
1298+
/*
1299+
* don't treat something with a leading zero followed by another
1300+
* digit as numeric - could be a zip code or similar
1301+
*/
1302+
if (src->len>0&& (src->data[0]!='0'|| !isdigit(src->data[1]))&&
1303+
strspn(src->data,"+-0123456789Ee.")==src->len)
1304+
{
1305+
/*
1306+
* might be a number. See if we can input it as a numeric
1307+
* value
1308+
*/
1309+
char*endptr="junk";
1310+
1311+
(void) (strtol(src->data,&endptr,10)+1);
1312+
if (*endptr=='\0')
1313+
{
1314+
/*
1315+
* strol man page says this means the whole string is
1316+
* valid
1317+
*/
1318+
is_number= true;
1319+
}
1320+
else
1321+
{
1322+
/* not an int - try a double */
1323+
(void) (strtod(src->data,&endptr)+1.0);
1324+
if (*endptr=='\0')
1325+
is_number= true;
1326+
}
1327+
}
1328+
if (is_number)
1329+
appendBinaryStringInfo(dst,src->data,src->len);
1330+
else
1331+
escape_json(dst,src->data);
1332+
}
1333+
strncpy(ptr,dst->data,dst->len);
1334+
ptr+=dst->len;
1335+
1336+
if (i+1!=count)
1337+
{
1338+
*ptr++=',';
1339+
*ptr++=' ';
1340+
}
1341+
}
1342+
*ptr++='}';
1343+
*ptr='\0';
1344+
1345+
PG_RETURN_TEXT_P(cstring_to_text(out));
1346+
}
1347+
1348+
PG_FUNCTION_INFO_V1(hstore_to_json);
1349+
Datumhstore_to_json(PG_FUNCTION_ARGS);
1350+
Datum
1351+
hstore_to_json(PG_FUNCTION_ARGS)
1352+
{
1353+
HStore*in=PG_GETARG_HS(0);
1354+
intbuflen,
1355+
i;
1356+
intcount=HS_COUNT(in);
1357+
char*out,
1358+
*ptr;
1359+
char*base=STRPTR(in);
1360+
HEntry*entries=ARRPTR(in);
1361+
StringInfosrc,
1362+
dst;
1363+
1364+
if (count==0)
1365+
{
1366+
out=palloc(1);
1367+
*out='\0';
1368+
PG_RETURN_TEXT_P(cstring_to_text(out));
1369+
}
1370+
1371+
buflen=3;
1372+
1373+
/*
1374+
* Formula adjusted slightly from the logic in hstore_out. We have to take
1375+
* account of out treatment of booleans to be a bit more pessimistic about
1376+
* the length of values.
1377+
*/
1378+
1379+
for (i=0;i<count;i++)
1380+
{
1381+
/* include "" and colon-space and comma-space */
1382+
buflen+=6+2*HS_KEYLEN(entries,i);
1383+
/* include "" only if nonnull */
1384+
buflen+=3+ (HS_VALISNULL(entries,i)
1385+
?1
1386+
:2*HS_VALLEN(entries,i));
1387+
}
1388+
1389+
out=ptr=palloc(buflen);
1390+
1391+
src=makeStringInfo();
1392+
dst=makeStringInfo();
1393+
1394+
*ptr++='{';
1395+
1396+
for (i=0;i<count;i++)
1397+
{
1398+
resetStringInfo(src);
1399+
resetStringInfo(dst);
1400+
appendBinaryStringInfo(src,HS_KEY(entries,base,i),HS_KEYLEN(entries,i));
1401+
escape_json(dst,src->data);
1402+
strncpy(ptr,dst->data,dst->len);
1403+
ptr+=dst->len;
1404+
*ptr++=':';
1405+
*ptr++=' ';
1406+
resetStringInfo(dst);
1407+
if (HS_VALISNULL(entries,i))
1408+
appendStringInfoString(dst,"null");
1409+
else
1410+
{
1411+
resetStringInfo(src);
1412+
appendBinaryStringInfo(src,HS_VAL(entries,base,i),HS_VALLEN(entries,i));
1413+
escape_json(dst,src->data);
1414+
}
1415+
strncpy(ptr,dst->data,dst->len);
1416+
ptr+=dst->len;
1417+
1418+
if (i+1!=count)
1419+
{
1420+
*ptr++=',';
1421+
*ptr++=' ';
1422+
}
1423+
}
1424+
*ptr++='}';
1425+
*ptr='\0';
1426+
1427+
PG_RETURN_TEXT_P(cstring_to_text(out));
1428+
}

‎contrib/hstore/sql/hstore.sql

Lines changed: 12 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -330,3 +330,15 @@ set enable_seqscan=off;
330330

331331
selectcount(*)from testhstorewhere h#># 'p=>1';
332332
selectcount(*)from testhstorewhere h='pos=>98, line=>371, node=>CBA, indexed=>t';
333+
334+
-- json
335+
select hstore_to_json('"a key" =>1, b => t, c => null, d=> 12345, e => 012345, f=> 1.234, g=> 2.345e+4');
336+
select cast( hstore'"a key" =>1, b => t, c => null, d=> 12345, e => 012345, f=> 1.234, g=> 2.345e+4'as json);
337+
select hstore_to_json_loose('"a key" =>1, b => t, c => null, d=> 12345, e => 012345, f=> 1.234, g=> 2.345e+4');
338+
339+
createtabletest_json_agg (f1text, f2 hstore);
340+
insert into test_json_aggvalues ('rec1','"a key" =>1, b => t, c => null, d=> 12345, e => 012345, f=> 1.234, g=> 2.345e+4'),
341+
('rec2','"a key" =>2, b => f, c => "null", d=> -12345, e => 012345.6, f=> -1.234, g=> 0.345e-4');
342+
select json_agg(q)from test_json_agg q;
343+
select json_agg(q)from (select f1, hstore_to_json_loose(f2)as f2from test_json_agg) q;
344+

‎doc/src/sgml/func.sgml

Lines changed: 48 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -9685,10 +9685,41 @@ table2-mapping
96859685
<entry><literal>row_to_json(row(1,'foo'))</literal></entry>
96869686
<entry><literal>{"f1":1,"f2":"foo"}</literal></entry>
96879687
</row>
9688+
<row>
9689+
<entry>
9690+
<indexterm>
9691+
<primary>to_json</primary>
9692+
</indexterm>
9693+
<literal>to_json(anyelement)</literal>
9694+
</entry>
9695+
<entry>
9696+
Returns the value as JSON. If the data type is not builtin, and there
9697+
is a cast from the type to json, the cast function will be used to
9698+
perform the conversion. Otherwise, for any value other than a number,
9699+
a boolean or NULL, the text representation will be used, escaped and
9700+
quoted so that it is legal JSON.
9701+
</entry>
9702+
<entry><literal>to_json('Fred said "Hi."'</literal></entry>
9703+
<entry><literal>"Fred said \"Hi.\""</literal></entry>
9704+
</row>
96889705
</tbody>
96899706
</tgroup>
96909707
</table>
96919708

9709+
<note>
9710+
<para>
9711+
The <xref linkend="hstore"> extension has a cast from hstore to
9712+
json, so that converted hstore values are represented as json objects,
9713+
not as string values.
9714+
</para>
9715+
</note>
9716+
9717+
<para>
9718+
See also <xref linkend="functions-aggregate"> about the aggregate
9719+
function <function>json_agg</function> which aggregates record
9720+
values as json efficiently.
9721+
</para>
9722+
96929723
</sect1>
96939724

96949725
<sect1 id="functions-sequence">
@@ -11059,6 +11090,22 @@ SELECT NULLIF(value, '(none)') ...
1105911090
<entry>equivalent to <function>bool_and</function></entry>
1106011091
</row>
1106111092

11093+
<row>
11094+
<entry>
11095+
<indexterm>
11096+
<primary>json_agg</primary>
11097+
</indexterm>
11098+
<function>json_agg(<replaceable class="parameter">record</replaceable>)</function>
11099+
</entry>
11100+
<entry>
11101+
<type>record</type>
11102+
</entry>
11103+
<entry>
11104+
<type>json</type>
11105+
</entry>
11106+
<entry>aggregates records as a json array of objects</entry>
11107+
</row>
11108+
1106211109
<row>
1106311110
<entry>
1106411111
<indexterm>
@@ -11204,6 +11251,7 @@ SELECT count(*) FROM sometable;
1120411251

1120511252
<para>
1120611253
The aggregate functions <function>array_agg</function>,
11254+
<function>json_agg</function>,
1120711255
<function>string_agg</function>,
1120811256
and <function>xmlagg</function>, as well as similar user-defined
1120911257
aggregate functions, produce meaningfully different result values

0 commit comments

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