Skip to content

Commit 31501d1

Browse files
author
Nikita Glukhov
committed
Map PG jsonb to SQL JSON type
1 parent c2078e7 commit 31501d1

File tree

9 files changed

+239
-2
lines changed

9 files changed

+239
-2
lines changed

‎doc/src/sgml/config.sgml

Lines changed: 17 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -9510,6 +9510,23 @@ dynamic_library_path = 'C:\tools\postgresql;H:\my_project\lib;$libdir'
95109510
</listitem>
95119511
</varlistentry>
95129512

9513+
<varlistentry id="guc-json-as-jsonb" xreflabel="json_as_jsonb">
9514+
<term><varname>json_as_jsonb</varname> (<type>boolean</type>)
9515+
<indexterm><primary>json</primary></indexterm>
9516+
<indexterm><primary>jsonb</primary></indexterm>
9517+
<indexterm>
9518+
<primary><varname>json_as_jsonb</varname> configuration parameter</primary>
9519+
</indexterm>
9520+
</term>
9521+
<listitem>
9522+
<para>
9523+
When on, SQL type <type>JSON</type> is mapped to
9524+
<productname>PostgreSQL</productname> type <type>jsonb</type>.
9525+
But SQL type <type>JSON TEXT</type> is always mapped to
9526+
<productname>PostgreSQL</productname> type <type>json</type>.
9527+
</para>
9528+
</listitem>
9529+
</varlistentry>
95139530
</variablelist>
95149531
</sect2>
95159532
</sect1>

‎src/backend/parser/gram.y

Lines changed: 29 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -63,6 +63,7 @@
6363
#include "storage/lmgr.h"
6464
#include "utils/date.h"
6565
#include "utils/datetime.h"
66+
#include "utils/jsonb.h"
6667
#include "utils/numeric.h"
6768
#include "utils/xml.h"
6869

@@ -527,7 +528,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
527528
%type <list> copy_options
528529

529530
%type <typnam> Typename SimpleTypename ConstTypename
530-
GenericType Numeric opt_float
531+
GenericType Numeric opt_float JsonType
531532
Character ConstCharacter
532533
CharacterWithLength CharacterWithoutLength
533534
ConstDatetime ConstInterval
@@ -663,7 +664,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
663664
INNER_P INOUT INPUT_P INSENSITIVE INSERT INSTEAD INT_P INTEGER
664665
INTERSECT INTERVAL INTO INVOKER IS ISNULL ISOLATION
665666

666-
JOIN
667+
JOIN JSON
667668

668669
KEY
669670

@@ -775,6 +776,8 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
775776
%left '*' '/' '%'
776777
%left '^'
777778
/* Unary Operators */
779+
%nonassoc JSON
780+
%left TEXT_P /* sets precedence for JSON TEXT */
778781
%left AT /* sets precedence for AT TIME ZONE */
779782
%left COLLATE
780783
%right UMINUS
@@ -12545,6 +12548,7 @@ SimpleTypename:
1254512548
$$->typmods = list_make2(makeIntConst(INTERVAL_FULL_RANGE, -1),
1254612549
makeIntConst($3, @3));
1254712550
}
12551+
| JsonType { $$ = $1; }
1254812552
;
1254912553

1255012554
/* We have a separate ConstTypename to allow defaulting fixed-length
@@ -12560,6 +12564,7 @@ SimpleTypename:
1256012564
*/
1256112565
ConstTypename:
1256212566
Numeric { $$ = $1; }
12567+
| JsonType { $$ = $1; }
1256312568
| ConstBit { $$ = $1; }
1256412569
| ConstCharacter { $$ = $1; }
1256512570
| ConstDatetime { $$ = $1; }
@@ -12931,6 +12936,20 @@ interval_second:
1293112936
}
1293212937
;
1293312938

12939+
/* Mapping of PG jsonb types to SQL/JSON JSON type */
12940+
JsonType:
12941+
JSON
12942+
{
12943+
$$ = SystemTypeName(json_as_jsonb ? "jsonb" : "json");
12944+
$$->location = @1;
12945+
}
12946+
| JSON TEXT_P
12947+
{
12948+
$$ = SystemTypeName("json");
12949+
$$->location = @1;
12950+
}
12951+
;
12952+
1293412953

1293512954
/*****************************************************************************
1293612955
*
@@ -13623,6 +13642,12 @@ c_expr: columnref { $$ = $1; }
1362313642
g->location = @1;
1362413643
$$ = (Node *)g;
1362513644
}
13645+
| JSON '(' a_expr ')'
13646+
{
13647+
List *typname = list_make1(makeString(json_as_jsonb ? "jsonb" : "json"));
13648+
13649+
$$ = (Node *) makeFuncCall(typname, list_make1($3), COERCE_EXPLICIT_CALL, @1);
13650+
}
1362613651
;
1362713652

1362813653
func_application: func_name '(' ')'
@@ -15468,6 +15493,7 @@ col_name_keyword:
1546815493
| INT_P
1546915494
| INTEGER
1547015495
| INTERVAL
15496+
| JSON
1547115497
| LEAST
1547215498
| NATIONAL
1547315499
| NCHAR
@@ -15813,6 +15839,7 @@ bare_label_keyword:
1581315839
| IS
1581415840
| ISOLATION
1581515841
| JOIN
15842+
| JSON
1581615843
| KEY
1581715844
| LABEL
1581815845
| LANGUAGE

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

Lines changed: 9 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -22,6 +22,7 @@
2222
#include "catalog/pg_type.h"
2323
#include "mb/pg_wchar.h"
2424
#include "utils/builtins.h"
25+
#include "utils/jsonb.h"
2526
#include "utils/lsyscache.h"
2627
#include "utils/numeric.h"
2728
#include "utils/syscache.h"
@@ -294,6 +295,14 @@ format_type_extended(Oid type_oid, int32 typemod, bits16 flags)
294295
else
295296
buf = pstrdup("character varying");
296297
break;
298+
299+
case JSONBOID:
300+
buf = pstrdup(json_as_jsonb ? "json" : "jsonb");
301+
break;
302+
303+
case JSONOID:
304+
buf = pstrdup(json_as_jsonb ? "json text" : "json");
305+
break;
297306
}
298307

299308
if (buf == NULL)

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

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -100,6 +100,8 @@ static char *JsonbToCStringWorker(StringInfo out, JsonbContainer *in,
100100
int estimated_len, JsonFormat format);
101101
static void add_indent(StringInfo out, bool indent, int level);
102102

103+
bool json_as_jsonb; /* GUC for mapping jsonb to SQL/JSON JSON */
104+
103105
/*
104106
* jsonb type input function
105107
*/

‎src/backend/utils/misc/guc.c

Lines changed: 12 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -90,6 +90,7 @@
9090
#include "utils/bytea.h"
9191
#include "utils/float.h"
9292
#include "utils/guc_tables.h"
93+
#include "utils/jsonb.h"
9394
#include "utils/memutils.h"
9495
#include "utils/pg_locale.h"
9596
#include "utils/pg_lsn.h"
@@ -2036,6 +2037,17 @@ static struct config_bool ConfigureNamesBool[] =
20362037
NULL, NULL, NULL
20372038
},
20382039

2040+
{
2041+
{"json_as_jsonb", PGC_USERSET, COMPAT_OPTIONS_CLIENT,
2042+
gettext_noop("Use jsonb type as default implementation of SQL JSON type."),
2043+
gettext_noop("When turned on, jsonb type is mapped to SQL JSON type, "
2044+
"json type is mapped to JSON TEXT type.")
2045+
},
2046+
&json_as_jsonb,
2047+
false,
2048+
NULL, NULL, NULL
2049+
},
2050+
20392051
/* End-of-list marker */
20402052
{
20412053
{NULL, 0, 0, NULL, NULL}, NULL, false, NULL, NULL, NULL

‎src/include/parser/kwlist.h

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -221,6 +221,7 @@ PG_KEYWORD("is", IS, TYPE_FUNC_NAME_KEYWORD, BARE_LABEL)
221221
PG_KEYWORD("isnull", ISNULL, TYPE_FUNC_NAME_KEYWORD, AS_LABEL)
222222
PG_KEYWORD("isolation", ISOLATION, UNRESERVED_KEYWORD, BARE_LABEL)
223223
PG_KEYWORD("join", JOIN, TYPE_FUNC_NAME_KEYWORD, BARE_LABEL)
224+
PG_KEYWORD("json", JSON, COL_NAME_KEYWORD, BARE_LABEL)
224225
PG_KEYWORD("key", KEY, UNRESERVED_KEYWORD, BARE_LABEL)
225226
PG_KEYWORD("label", LABEL, UNRESERVED_KEYWORD, BARE_LABEL)
226227
PG_KEYWORD("language", LANGUAGE, UNRESERVED_KEYWORD, BARE_LABEL)

‎src/include/utils/jsonb.h

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -402,5 +402,6 @@ extern void JsonbHashScalarValueExtended(const JsonbValue *scalarVal,
402402
extern int reserveFromBuffer(StringInfo buffer, int len);
403403
extern void appendToBuffer(StringInfo buffer, const void *data, int len);
404404

405+
extern bool json_as_jsonb; /* GUC */
405406

406407
#endif /* __JSONB_H__ */

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

Lines changed: 130 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -5017,3 +5017,133 @@ select '12345.0000000000000000000000000000000000000000000005'::jsonb::int8;
50175017
12345
50185018
(1 row)
50195019

5020+
-- test mapping of jsonb to SQL/JSON JSON type
5021+
select json(' { "aa": 1, "b" : 2 }');
5022+
json
5023+
-----------------------
5024+
{ "aa": 1, "b" : 2 }
5025+
(1 row)
5026+
5027+
select json ' { "aa": 1, "b" : 2 }';
5028+
json
5029+
-----------------------
5030+
{ "aa": 1, "b" : 2 }
5031+
(1 row)
5032+
5033+
select json text ' { "aa": 1, "b" : 2 }';
5034+
json
5035+
-----------------------
5036+
{ "aa": 1, "b" : 2 }
5037+
(1 row)
5038+
5039+
create table test_json_as_json (js json, jb jsonb);
5040+
\d test_json_as_json
5041+
Table "public.test_json_as_json"
5042+
Column | Type | Collation | Nullable | Default
5043+
--------+-------+-----------+----------+---------
5044+
js | json | | |
5045+
jb | jsonb | | |
5046+
5047+
set json_as_jsonb = on;
5048+
select json(' { "aa": 1, "b" : 2 }');
5049+
jsonb
5050+
-------------------
5051+
{"b": 2, "aa": 1}
5052+
(1 row)
5053+
5054+
select json ' { "aa": 1, "b" : 2 }';
5055+
jsonb
5056+
-------------------
5057+
{"b": 2, "aa": 1}
5058+
(1 row)
5059+
5060+
select json text ' { "aa": 1, "b" : 2 }';
5061+
json
5062+
-----------------------
5063+
{ "aa": 1, "b" : 2 }
5064+
(1 row)
5065+
5066+
\d test_json_as_json
5067+
Table "public.test_json_as_json"
5068+
Column | Type | Collation | Nullable | Default
5069+
--------+-----------+-----------+----------+---------
5070+
js | json text | | |
5071+
jb | json | | |
5072+
5073+
create table test_json_as_jsonb (js json, jb jsonb, jt json text);
5074+
\d test_json_as_jsonb
5075+
Table "public.test_json_as_jsonb"
5076+
Column | Type | Collation | Nullable | Default
5077+
--------+-----------+-----------+----------+---------
5078+
js | json | | |
5079+
jb | json | | |
5080+
jt | json text | | |
5081+
5082+
insert into test_json_as_jsonb values ('{ "a" : 1 }', '{ "a" : 1 }', '{ "a" : 1 }');
5083+
select * from test_json_as_jsonb;
5084+
js | jb | jt
5085+
----------+----------+-------------
5086+
{"a": 1} | {"a": 1} | { "a" : 1 }
5087+
(1 row)
5088+
5089+
select jsonb_object_field(js, 'a') from test_json_as_jsonb;
5090+
jsonb_object_field
5091+
--------------------
5092+
1
5093+
(1 row)
5094+
5095+
select jsonb_object_field(jb, 'a') from test_json_as_jsonb;
5096+
jsonb_object_field
5097+
--------------------
5098+
1
5099+
(1 row)
5100+
5101+
select jsonb_object_field(jt, 'a') from test_json_as_jsonb;
5102+
ERROR: function jsonb_object_field(json text, unknown) does not exist
5103+
LINE 1: select jsonb_object_field(jt, 'a') from test_json_as_jsonb;
5104+
^
5105+
HINT: No function matches the given name and argument types. You might need to add explicit type casts.
5106+
select json_object_field(jt, 'a') from test_json_as_jsonb;
5107+
json_object_field
5108+
-------------------
5109+
1
5110+
(1 row)
5111+
5112+
set json_as_jsonb = off;
5113+
\d test_json_as_jsonb
5114+
Table "public.test_json_as_jsonb"
5115+
Column | Type | Collation | Nullable | Default
5116+
--------+-------+-----------+----------+---------
5117+
js | jsonb | | |
5118+
jb | jsonb | | |
5119+
jt | json | | |
5120+
5121+
select * from test_json_as_jsonb;
5122+
js | jb | jt
5123+
----------+----------+-------------
5124+
{"a": 1} | {"a": 1} | { "a" : 1 }
5125+
(1 row)
5126+
5127+
select jsonb_object_field(js, 'a') from test_json_as_jsonb;
5128+
jsonb_object_field
5129+
--------------------
5130+
1
5131+
(1 row)
5132+
5133+
select jsonb_object_field(jb, 'a') from test_json_as_jsonb;
5134+
jsonb_object_field
5135+
--------------------
5136+
1
5137+
(1 row)
5138+
5139+
select jsonb_object_field(jt, 'a') from test_json_as_jsonb;
5140+
ERROR: function jsonb_object_field(json, unknown) does not exist
5141+
LINE 1: select jsonb_object_field(jt, 'a') from test_json_as_jsonb;
5142+
^
5143+
HINT: No function matches the given name and argument types. You might need to add explicit type casts.
5144+
select json_object_field(jt, 'a') from test_json_as_jsonb;
5145+
json_object_field
5146+
-------------------
5147+
1
5148+
(1 row)
5149+

‎src/test/regress/sql/jsonb.sql

Lines changed: 38 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1280,3 +1280,41 @@ select '12345.0000000000000000000000000000000000000000000005'::jsonb::float8;
12801280
select '12345.0000000000000000000000000000000000000000000005'::jsonb::int2;
12811281
select '12345.0000000000000000000000000000000000000000000005'::jsonb::int4;
12821282
select '12345.0000000000000000000000000000000000000000000005'::jsonb::int8;
1283+
1284+
-- test mapping of jsonb to SQL/JSON JSON type
1285+
select json(' { "aa": 1, "b" : 2 }');
1286+
select json ' { "aa": 1, "b" : 2 }';
1287+
select json text ' { "aa": 1, "b" : 2 }';
1288+
1289+
create table test_json_as_json (js json, jb jsonb);
1290+
\d test_json_as_json
1291+
1292+
set json_as_jsonb = on;
1293+
1294+
select json(' { "aa": 1, "b" : 2 }');
1295+
select json ' { "aa": 1, "b" : 2 }';
1296+
select json text ' { "aa": 1, "b" : 2 }';
1297+
1298+
\d test_json_as_json
1299+
1300+
create table test_json_as_jsonb (js json, jb jsonb, jt json text);
1301+
\d test_json_as_jsonb
1302+
1303+
insert into test_json_as_jsonb values ('{ "a" : 1 }', '{ "a" : 1 }', '{ "a" : 1 }');
1304+
1305+
select * from test_json_as_jsonb;
1306+
1307+
select jsonb_object_field(js, 'a') from test_json_as_jsonb;
1308+
select jsonb_object_field(jb, 'a') from test_json_as_jsonb;
1309+
select jsonb_object_field(jt, 'a') from test_json_as_jsonb;
1310+
select json_object_field(jt, 'a') from test_json_as_jsonb;
1311+
1312+
set json_as_jsonb = off;
1313+
\d test_json_as_jsonb
1314+
1315+
select * from test_json_as_jsonb;
1316+
1317+
select jsonb_object_field(js, 'a') from test_json_as_jsonb;
1318+
select jsonb_object_field(jb, 'a') from test_json_as_jsonb;
1319+
select jsonb_object_field(jt, 'a') from test_json_as_jsonb;
1320+
select json_object_field(jt, 'a') from test_json_as_jsonb;

0 commit comments

Comments
 (0)