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

Commitd9134d0

Browse files
committed
Introduce jsonb, a structured format for storing json.
The new format accepts exactly the same data as the json type. However, it isstored in a format that does not require reparsing the orgiginal text in orderto process it, making it much more suitable for indexing and other operations.Insignificant whitespace is discarded, and the order of object keys is notpreserved. Neither are duplicate object keys kept - the later value for a givenkey is the only one stored.The new type has all the functions and operators that the json type has,with the exception of the json generation functions (to_json, json_agg etc.)and with identical semantics. In addition, there are operator classes forhash and btree indexing, and two classes for GIN indexing, that have noequivalent in the json type.This feature grew out of previous work by Oleg Bartunov and Teodor Sigaev, whichwas intended to provide similar facilities to a nested hstore type, but whichin the end proved to have some significant compatibility issues.Authors: Oleg Bartunov, Teodor Sigaev, Peter Geoghegan and Andrew Dunstan.Review: Andres Freund
1 parentb2b2491 commitd9134d0

Some content is hidden

Large Commits have some content hidden by default. Use the searchbox below for content that may be hidden.

43 files changed

+11662
-381
lines changed

‎contrib/hstore/Makefile

Lines changed: 2 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -5,7 +5,8 @@ OBJS = hstore_io.o hstore_op.o hstore_gist.o hstore_gin.o hstore_compat.o \
55
crc32.o
66

77
EXTENSION = hstore
8-
DATA = hstore--1.2.sql hstore--1.1--1.2.sql hstore--1.0--1.1.sql\
8+
DATA = hstore--1.3.sql hstore--1.2--1.3.sql\
9+
hstore--1.1--1.2.sql hstore--1.0--1.1.sql\
910
hstore--unpackaged--1.0.sql
1011

1112
REGRESS = hstore

‎contrib/hstore/expected/hstore.out

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

1456-
-- json
1456+
-- json and jsonb
14571457
select hstore_to_json('"a key" =>1, b => t, c => null, d=> 12345, e => 012345, f=> 1.234, g=> 2.345e+4');
14581458
hstore_to_json
14591459
-------------------------------------------------------------------------------------------------
@@ -1472,6 +1472,24 @@ select hstore_to_json_loose('"a key" =>1, b => t, c => null, d=> 12345, e => 012
14721472
{"b": true, "c": null, "d": 12345, "e": "012345", "f": 1.234, "g": 2.345e+4, "a key": 1}
14731473
(1 row)
14741474

1475+
select hstore_to_jsonb('"a key" =>1, b => t, c => null, d=> 12345, e => 012345, f=> 1.234, g=> 2.345e+4');
1476+
hstore_to_jsonb
1477+
-------------------------------------------------------------------------------------------------
1478+
{"b": "t", "c": null, "d": "12345", "e": "012345", "f": "1.234", "g": "2.345e+4", "a key": "1"}
1479+
(1 row)
1480+
1481+
select cast( hstore '"a key" =>1, b => t, c => null, d=> 12345, e => 012345, f=> 1.234, g=> 2.345e+4' as jsonb);
1482+
jsonb
1483+
-------------------------------------------------------------------------------------------------
1484+
{"b": "t", "c": null, "d": "12345", "e": "012345", "f": "1.234", "g": "2.345e+4", "a key": "1"}
1485+
(1 row)
1486+
1487+
select hstore_to_jsonb_loose('"a key" =>1, b => t, c => null, d=> 12345, e => 012345, f=> 1.234, g=> 2.345e+4');
1488+
hstore_to_jsonb_loose
1489+
---------------------------------------------------------------------------------------
1490+
{"b": true, "c": null, "d": 12345, "e": "012345", "f": 1.234, "g": 23450, "a key": 1}
1491+
(1 row)
1492+
14751493
create table test_json_agg (f1 text, f2 hstore);
14761494
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'),
14771495
('rec2','"a key" =>2, b => f, c => "null", d=> -12345, e => 012345.6, f=> -1.234, g=> 0.345e-4');

‎contrib/hstore/hstore--1.2--1.3.sql

Lines changed: 17 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,17 @@
1+
/* contrib/hstore/hstore--1.2--1.3.sql*/
2+
3+
-- complain if script is sourced in psql, rather than via ALTER EXTENSION
4+
\echo Use"ALTER EXTENSION hstore UPDATE TO '1.3'" to load this file. \quit
5+
6+
CREATEFUNCTIONhstore_to_jsonb(hstore)
7+
RETURNS jsonb
8+
AS'MODULE_PATHNAME','hstore_to_jsonb'
9+
LANGUAGE C IMMUTABLE STRICT;
10+
11+
CREATE CAST (hstoreAS jsonb)
12+
WITH FUNCTION hstore_to_jsonb(hstore);
13+
14+
CREATEFUNCTIONhstore_to_jsonb_loose(hstore)
15+
RETURNS jsonb
16+
AS'MODULE_PATHNAME','hstore_to_jsonb_loose'
17+
LANGUAGE C IMMUTABLE STRICT;

‎contrib/hstore/hstore--1.2.sqlrenamed to‎contrib/hstore/hstore--1.3.sql

Lines changed: 14 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1,4 +1,4 @@
1-
/* contrib/hstore/hstore--1.1.sql*/
1+
/* contrib/hstore/hstore--1.3.sql*/
22

33
-- complain if script is sourced in psql, rather than via CREATE EXTENSION
44
\echo Use"CREATE EXTENSION hstore" to load this file. \quit
@@ -247,6 +247,19 @@ RETURNS json
247247
AS'MODULE_PATHNAME','hstore_to_json_loose'
248248
LANGUAGE C IMMUTABLE STRICT;
249249

250+
CREATEFUNCTIONhstore_to_jsonb(hstore)
251+
RETURNS jsonb
252+
AS'MODULE_PATHNAME','hstore_to_jsonb'
253+
LANGUAGE C IMMUTABLE STRICT;
254+
255+
CREATE CAST (hstoreAS jsonb)
256+
WITH FUNCTION hstore_to_jsonb(hstore);
257+
258+
CREATEFUNCTIONhstore_to_jsonb_loose(hstore)
259+
RETURNS jsonb
260+
AS'MODULE_PATHNAME','hstore_to_jsonb_loose'
261+
LANGUAGE C IMMUTABLE STRICT;
262+
250263
CREATEFUNCTIONhstore(record)
251264
RETURNS hstore
252265
AS'MODULE_PATHNAME','hstore_from_record'

‎contrib/hstore/hstore.control

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,5 @@
11
# hstore extension
22
comment = 'data type for storing sets of (key, value) pairs'
3-
default_version = '1.2'
3+
default_version = '1.3'
44
module_pathname = '$libdir/hstore'
55
relocatable = true

‎contrib/hstore/hstore_io.c

Lines changed: 165 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -12,6 +12,7 @@
1212
#include"libpq/pqformat.h"
1313
#include"utils/builtins.h"
1414
#include"utils/json.h"
15+
#include"utils/jsonb.h"
1516
#include"utils/lsyscache.h"
1617
#include"utils/memutils.h"
1718
#include"utils/typcache.h"
@@ -1374,3 +1375,167 @@ hstore_to_json(PG_FUNCTION_ARGS)
13741375

13751376
PG_RETURN_TEXT_P(cstring_to_text(dst.data));
13761377
}
1378+
1379+
PG_FUNCTION_INFO_V1(hstore_to_jsonb);
1380+
Datumhstore_to_jsonb(PG_FUNCTION_ARGS);
1381+
Datum
1382+
hstore_to_jsonb(PG_FUNCTION_ARGS)
1383+
{
1384+
HStore*in=PG_GETARG_HS(0);
1385+
inti;
1386+
intcount=HS_COUNT(in);
1387+
char*base=STRPTR(in);
1388+
HEntry*entries=ARRPTR(in);
1389+
JsonbParseState*state=NULL;
1390+
JsonbValue*res;
1391+
1392+
res=pushJsonbValue(&state,WJB_BEGIN_OBJECT,NULL);
1393+
1394+
for (i=0;i<count;i++)
1395+
{
1396+
JsonbValuekey,val;
1397+
1398+
key.estSize=sizeof(JEntry);
1399+
key.type=jbvString;
1400+
key.string.len=HS_KEYLEN(entries,i);
1401+
key.string.val=pnstrdup(HS_KEY(entries,base,i),key.string.len);
1402+
key.estSize+=key.string.len;
1403+
1404+
res=pushJsonbValue(&state,WJB_KEY,&key);
1405+
1406+
if (HS_VALISNULL(entries,i))
1407+
{
1408+
val.estSize=sizeof(JEntry);
1409+
val.type=jbvNull;
1410+
}
1411+
else
1412+
{
1413+
val.estSize=sizeof(JEntry);
1414+
val.type=jbvString;
1415+
val.string.len=HS_VALLEN(entries,i);
1416+
val.string.val=pnstrdup(HS_VAL(entries,base,i),val.string.len);
1417+
val.estSize+=val.string.len;
1418+
}
1419+
res=pushJsonbValue(&state,WJB_VALUE,&val);
1420+
}
1421+
1422+
res=pushJsonbValue(&state,WJB_END_OBJECT,NULL);
1423+
1424+
PG_RETURN_POINTER(JsonbValueToJsonb(res));
1425+
}
1426+
1427+
PG_FUNCTION_INFO_V1(hstore_to_jsonb_loose);
1428+
Datumhstore_to_jsonb_loose(PG_FUNCTION_ARGS);
1429+
Datum
1430+
hstore_to_jsonb_loose(PG_FUNCTION_ARGS)
1431+
{
1432+
HStore*in=PG_GETARG_HS(0);
1433+
inti;
1434+
intcount=HS_COUNT(in);
1435+
char*base=STRPTR(in);
1436+
HEntry*entries=ARRPTR(in);
1437+
JsonbParseState*state=NULL;
1438+
JsonbValue*res;
1439+
StringInfoDatatmp;
1440+
boolis_number;
1441+
1442+
initStringInfo(&tmp);
1443+
1444+
res=pushJsonbValue(&state,WJB_BEGIN_OBJECT,NULL);
1445+
1446+
for (i=0;i<count;i++)
1447+
{
1448+
JsonbValuekey,val;
1449+
1450+
key.estSize=sizeof(JEntry);
1451+
key.type=jbvString;
1452+
key.string.len=HS_KEYLEN(entries,i);
1453+
key.string.val=pnstrdup(HS_KEY(entries,base,i),key.string.len);
1454+
key.estSize+=key.string.len;
1455+
1456+
res=pushJsonbValue(&state,WJB_KEY,&key);
1457+
1458+
val.estSize=sizeof(JEntry);
1459+
1460+
if (HS_VALISNULL(entries,i))
1461+
{
1462+
val.type=jbvNull;
1463+
}
1464+
/* guess that values of 't' or 'f' are booleans */
1465+
elseif (HS_VALLEN(entries,i)==1&&*(HS_VAL(entries,base,i))=='t')
1466+
{
1467+
val.type=jbvBool;
1468+
val.boolean= true;
1469+
}
1470+
elseif (HS_VALLEN(entries,i)==1&&*(HS_VAL(entries,base,i))=='f')
1471+
{
1472+
val.type=jbvBool;
1473+
val.boolean= false;
1474+
}
1475+
else
1476+
{
1477+
is_number= false;
1478+
resetStringInfo(&tmp);
1479+
1480+
appendBinaryStringInfo(&tmp,HS_VAL(entries,base,i),HS_VALLEN(entries,i));
1481+
1482+
/*
1483+
* don't treat something with a leading zero followed by another
1484+
* digit as numeric - could be a zip code or similar
1485+
*/
1486+
if (tmp.len>0&&
1487+
!(tmp.data[0]=='0'&&
1488+
isdigit((unsignedchar)tmp.data[1]))&&
1489+
strspn(tmp.data,"+-0123456789Ee.")==tmp.len)
1490+
{
1491+
/*
1492+
* might be a number. See if we can input it as a numeric
1493+
* value. Ignore any actual parsed value.
1494+
*/
1495+
char*endptr="junk";
1496+
longlval;
1497+
1498+
lval=strtol(tmp.data,&endptr,10);
1499+
(void)lval;
1500+
if (*endptr=='\0')
1501+
{
1502+
/*
1503+
* strol man page says this means the whole string is
1504+
* valid
1505+
*/
1506+
is_number= true;
1507+
}
1508+
else
1509+
{
1510+
/* not an int - try a double */
1511+
doubledval;
1512+
1513+
dval=strtod(tmp.data,&endptr);
1514+
(void)dval;
1515+
if (*endptr=='\0')
1516+
is_number= true;
1517+
}
1518+
}
1519+
if (is_number)
1520+
{
1521+
val.type=jbvNumeric;
1522+
val.numeric=DatumGetNumeric(
1523+
DirectFunctionCall3(numeric_in,CStringGetDatum(tmp.data),0,-1));
1524+
val.estSize+=VARSIZE_ANY(val.numeric)+sizeof(JEntry);
1525+
}
1526+
else
1527+
{
1528+
val.estSize=sizeof(JEntry);
1529+
val.type=jbvString;
1530+
val.string.len=HS_VALLEN(entries,i);
1531+
val.string.val=pnstrdup(HS_VAL(entries,base,i),val.string.len);
1532+
val.estSize+=val.string.len;
1533+
}
1534+
}
1535+
res=pushJsonbValue(&state,WJB_VALUE,&val);
1536+
}
1537+
1538+
res=pushJsonbValue(&state,WJB_END_OBJECT,NULL);
1539+
1540+
PG_RETURN_POINTER(JsonbValueToJsonb(res));
1541+
}

‎contrib/hstore/sql/hstore.sql

Lines changed: 5 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -331,11 +331,15 @@ set enable_seqscan=off;
331331
selectcount(*)from testhstorewhere h#># 'p=>1';
332332
selectcount(*)from testhstorewhere h='pos=>98, line=>371, node=>CBA, indexed=>t';
333333

334-
-- json
334+
-- json and jsonb
335335
select hstore_to_json('"a key" =>1, b => t, c => null, d=> 12345, e => 012345, f=> 1.234, g=> 2.345e+4');
336336
select cast( hstore'"a key" =>1, b => t, c => null, d=> 12345, e => 012345, f=> 1.234, g=> 2.345e+4'as json);
337337
select hstore_to_json_loose('"a key" =>1, b => t, c => null, d=> 12345, e => 012345, f=> 1.234, g=> 2.345e+4');
338338

339+
select hstore_to_jsonb('"a key" =>1, b => t, c => null, d=> 12345, e => 012345, f=> 1.234, g=> 2.345e+4');
340+
select cast( hstore'"a key" =>1, b => t, c => null, d=> 12345, e => 012345, f=> 1.234, g=> 2.345e+4'as jsonb);
341+
select hstore_to_jsonb_loose('"a key" =>1, b => t, c => null, d=> 12345, e => 012345, f=> 1.234, g=> 2.345e+4');
342+
339343
createtabletest_json_agg (f1text, f2 hstore);
340344
insert into test_json_aggvalues ('rec1','"a key" =>1, b => t, c => null, d=> 12345, e => 012345, f=> 1.234, g=> 2.345e+4'),
341345
('rec2','"a key" =>2, b => f, c => "null", d=> -12345, e => 012345.6, f=> -1.234, g=> 0.345e-4');

‎doc/src/sgml/datatype.sgml

Lines changed: 8 additions & 29 deletions
Original file line numberDiff line numberDiff line change
@@ -139,7 +139,13 @@
139139
<row>
140140
<entry><type>json</type></entry>
141141
<entry></entry>
142-
<entry>JSON data</entry>
142+
<entry>textual JSON data</entry>
143+
</row>
144+
145+
<row>
146+
<entry><type>jsonb</type></entry>
147+
<entry></entry>
148+
<entry>binary JSON data, decomposed</entry>
143149
</row>
144150

145151
<row>
@@ -4220,34 +4226,7 @@ SET xmloption TO { DOCUMENT | CONTENT };
42204226
</sect2>
42214227
</sect1>
42224228

4223-
<sect1 id="datatype-json">
4224-
<title><acronym>JSON</> Type</title>
4225-
4226-
<indexterm zone="datatype-json">
4227-
<primary>JSON</primary>
4228-
</indexterm>
4229-
4230-
<para>
4231-
The <type>json</type> data type can be used to store JSON (JavaScript
4232-
Object Notation) data, as specified in <ulink
4233-
url="http://www.ietf.org/rfc/rfc4627.txt">RFC 4627</ulink>. Such
4234-
data can also be stored as <type>text</type>, but the
4235-
<type>json</type> data type has the advantage of checking that each
4236-
stored value is a valid JSON value. There are also related support
4237-
functions available; see <xref linkend="functions-json">.
4238-
</para>
4239-
4240-
<para>
4241-
<productname>PostgreSQL</productname> allows only one server encoding
4242-
per database. It is therefore not possible for JSON to conform rigidly
4243-
to the specification unless the server encoding is UTF-8. Attempts to
4244-
directly include characters which cannot be represented in the server
4245-
encoding will fail; conversely, characters which can be represented in
4246-
the server encoding but not in UTF-8 will be allowed.
4247-
<literal>\uXXXX</literal> escapes are allowed regardless of the server
4248-
encoding, and are checked only for syntactic correctness.
4249-
</para>
4250-
</sect1>
4229+
&json;
42514230

42524231
&array;
42534232

‎doc/src/sgml/filelist.sgml

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -22,6 +22,7 @@
2222
<!ENTITY dml SYSTEM "dml.sgml">
2323
<!ENTITY func SYSTEM "func.sgml">
2424
<!ENTITY indices SYSTEM "indices.sgml">
25+
<!ENTITY json SYSTEM "json.sgml">
2526
<!ENTITY mvcc SYSTEM "mvcc.sgml">
2627
<!ENTITY perform SYSTEM "perform.sgml">
2728
<!ENTITY queries SYSTEM "queries.sgml">

0 commit comments

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