Help Center/ GaussDB/ M-Compatibility Developer Guide(Centralized)/ SQL Reference/ Data Types/ JSON Types
Updated on 2025-10-23 GMT+08:00
View PDF
Share

JSON Types

Table 1 lists the JSON types supported by M-compatible databases.

Table 1 JSON types

Name

Description

Storage Space

JSON

Unstructured data type, which is used to store and operate JSON data.

  • Input formats:
    Scalar:
    • Empty type: null, and all letters are lowercase.
    • Boolean type: Only true and false are supported, and all letters are lowercase.
    • Number type: positive integer, negative integer, decimal, and 0. Scientific notation is supported. Redundant leading zeros, plus signs (+) before positive numbers, NaN, and inf are not supported.
    • Character string type: The value must be enclosed in double quotation marks.

    Array: [] structure. The stored elements can be JSON of any type. The types of all elements in the array do not need to be the same.

    Object: {} structure, which stores the key-value pair of {key:value}. key can only be a string containing double quotation marks, and value can be any type of JSON. For duplicate keys, the first key-value pair {key:value} prevails.

  • Output formats:

    After the text is parsed into the JSON type, irrelevant semantic details, such as spaces, are ignored.

    The formats of object types are normalized as follows:

    • Delete the key-value pair {key:value} with duplicate keys and retain only the first key-value pair {key:value}.
    • The key-value pair {key:value} is re-sorted. The sorting rule is as follows: The key with a longer length is placed at the end. If the key lengths are the same, the key with a larger ASCII code is placed at the end.
  • Value range:
    • The value range of the numeric type is the same as that of the FLOAT8 type. The minimum value is -1.797693e+308, and the maximum value is 1.797693e+308. If the value exceeds the value range, an error is reported.
    • The maximum nesting depth of array and object types is 100.

Up to 1073741817 bytes
  • The validity check of the JSON data type is not affected by the GUC parameter sql_mode. If sql_mode is set to any value and invalid characters are entered in the JSON column, an error is generated.
  • The JSON data type cannot be used as a primary key, index key, or partition key.
  • If the integer inserted into a column of the JSON type is greater than the maximum value of the unsigned integer type (2^64 - 1) or less than the minimum value of the signed integer type (-2^63), the integer is stored as the DOUBLE type, and the precision is partially different.
  • To distinguish the JSON type in GaussDB's non-M-compatible mode from that in M-compatible mode, the compatibility option cast_as_new_json has been added to the GUC parameter m_format_behavior_compat_options in GaussDB's M-compatible mode.
    • When this option is enabled, operations such as ::JSON, CREATE TABLE <tablename> AS <SELECT containing JSON type>, and other scenarios involving JSON type conversion through ::JSON will actually convert data to the JSON type in the M-compatible mode.
    • If this option is not enabled, the relevant JSON type is converted to the JSON type in a GaussDB's non-M-compatible database.
    • This option is enabled by default after the database instance is initialized. However, if you need to explicitly specify the value of the m_format_behavior_compat_options compatibility parameter and use the JSON type in an M-compatible database, you need to set the cast_as_new_json parameter to ensure that the function meets the expectation. Example: 
      SET m_format_behavior_compat_options = 'xxx,xxx,cast_as_new_json';
  • To distinguish from the original JSON type, the typname of the JSON type in M compatibility in the pg_type system catalog is jsonm.
    • In the SQL syntax, JSON is equivalent to an alias of JSONM, but JSONM cannot be used as a data type name.
    • jsonm may appear in texts such as error messages. You can consider it as the JSON type.
    • In the pg_type system catalog, the data types whose typname is json or jsonb are the original JSON types and have been deprecated in M compatibility. Do not use related internal functions, such as json_in or jsonb_in.

Examples

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
 
-- Create a table whose column type is JSON.
m_db=# CREATE TABLE test_json (id INT, json_value JSON);
CREATE TABLE
-- Insert an empty scalar.
m_db=# INSERT INTO test_json VALUES(1, 'null');
INSERT 0 1
-- Insert a scalar of the number type.
m_db=# INSERT INTO test_json VALUES(2, '-1.5e+2');
INSERT 0 1
-- Insert a scalar of the Boolean type.
m_db=# INSERT INTO test_json VALUES(3, 'true');
INSERT 0 1
m_db=# INSERT INTO test_json VALUES(4, 'false');
INSERT 0 1
-- Insert a scalar of the string type.
m_db=# INSERT INTO test_json VALUES(5, '"abc"');
INSERT 0 1
-- Insert an array.
m_db=# INSERT INTO test_json VALUES(6, '[1, 2, "json", null, [[]], {}]');
INSERT 0 1
-- Insert an object.
m_db=# INSERT INTO test_json VALUES(7, '{"jsnid": [true, "abc"], "tag": {"ab": 1, "b": null, "a": 2}}');
INSERT 0 1
-- Query data in the table.
m_db=# SELECT * FROM test_json;
 id |                          json_value                           
----+---------------------------------------------------------------
  1 | null
  2 | -150
  3 | true
  4 | false
  5 | "abc"
  6 | [1, 2, "json", null, [[]], {}]
  7 | {"tag": {"a": 2, "b": null, "ab": 1}, "jsnid": [true, "abc"]}
(7 rows)

-- An error is reported when the input is invalid.
m_db=# INSERT INTO test_json VALUES(1, '+20');
ERROR:  invalid input syntax for type json
LINE 1: INSERT INTO test_json VALUES(1, '+20');
                                        ^
DETAIL:  Token "+" is invalid.
CONTEXT:  JSON data, line 1: +...
referenced column: json_value
m_db=# INSERT INTO test_json VALUES(1, 'NaN');
ERROR:  invalid input syntax for type json
LINE 1: INSERT INTO test_json VALUES(1, 'NaN');
                                        ^
DETAIL:  Token "NaN" is invalid.
CONTEXT:  JSON data, line 1: NaN
referenced column: json_value
m_db=# INSERT INTO test_json VALUES(1, 'inf');
ERROR:  invalid input syntax for type json
LINE 1: INSERT INTO test_json VALUES(1, 'inf');
                                        ^
DETAIL:  Token "inf" is invalid.
CONTEXT:  JSON data, line 1: inf
referenced column: json_value
m_db=# INSERT INTO test_json VALUES(1, 'NULL');
ERROR:  invalid input syntax for type json
LINE 1: INSERT INTO test_json VALUES(1, 'NULL');
                                        ^
DETAIL:  Token "NULL" is invalid.
CONTEXT:  JSON data, line 1: NULL
referenced column: json_value
m_db=# INSERT INTO test_json VALUES(1, 'TRUE');
ERROR:  invalid input syntax for type json
LINE 1: INSERT INTO test_json VALUES(1, 'TRUE');
                                        ^
DETAIL:  Token "TRUE" is invalid.
CONTEXT:  JSON data, line 1: TRUE
referenced column: json_value
m_db=# INSERT INTO test_json VALUES(1, '000123');
ERROR:  invalid input syntax for type json
LINE 1: INSERT INTO test_json VALUES(1, '000123');
                                        ^
DETAIL:  Token "000123" is invalid.
CONTEXT:  JSON data, line 1: 000123
referenced column: json_value
m_db=# INSERT INTO test_json VALUES(1, 'abc');
ERROR:  invalid input syntax for type json
LINE 1: INSERT INTO test_json VALUES(1, 'abc');
                                        ^
DETAIL:  Token "abc" is invalid.
CONTEXT:  JSON data, line 1: abc
referenced column: json_value
m_db=# INSERT INTO test_json VALUES(1, '{12:"abc"}');
ERROR:  invalid input syntax for type json
LINE 1: INSERT INTO test_json VALUES(1, '{12:"abc"}');
                                        ^
DETAIL:  Expected string or "}", but found "12".
CONTEXT:  JSON data, line 1: {12...
referenced column: json_value

-- Drop the table.
m_db=# DROP TABLE test_json;
Parent topic: Data Types