JSON 数据类型的输入/输出语法如RFC 7159.

以下都是有效的 json（或 jsonb）表达式

-- Simple scalar/primitive value
-- Primitive values can be numbers, quoted strings, true, false, or null
SELECT '5'::json;

-- Array of zero or more elements (elements need not be of same type)
SELECT '[1, 2, "foo", null]'::json;

-- Object containing pairs of keys and values
-- Note that object keys must always be quoted strings
SELECT '{"bar": "baz", "balance": 7.77, "active": false}'::json;

-- Arrays and objects can be nested arbitrarily
SELECT '{"foo": [true, "bar"], "tags": {"a": 1, "b": null}}'::json;

如前所述，当输入 JSON 值，然后在没有任何额外处理的情况下打印时，json 输出与输入相同的文本，而 jsonb 不保留语义上不重要的细节，例如空格。例如，请注意这里的差异

SELECT '{"bar": "baz", "balance": 7.77, "active":false}'::json;
                      json
-------------------------------------------------
 {"bar": "baz", "balance": 7.77, "active":false}
(1 row)

SELECT '{"bar": "baz", "balance": 7.77, "active":false}'::jsonb;
                      jsonb
--------------------------------------------------
 {"bar": "baz", "active": false, "balance": 7.77}
(1 row)

一个值得注意的语义上不重要的细节是，在 jsonb 中，数字将根据底层 numeric 类型的行为进行打印。实际上，这意味着以 E 表示法输入的数字将被打印时不带该表示法，例如

SELECT '{"reading": 1.230e-5}'::json, '{"reading": 1.230e-5}'::jsonb;
         json          |          jsonb
-----------------------+-------------------------
 {"reading": 1.230e-5} | {"reading": 0.00001230}
(1 row)

但是，jsonb 将保留尾随的小数零，如本例所示，即使这些零对于诸如相等性检查之类的目的是语义上不重要的。

有关可用于构建和处理 JSON 值的内置函数和运算符的列表，请参阅第 9.16 节。

以 JSON 形式表示数据可能比传统的关联数据模型灵活得多，这在需求不确定的环境中很有吸引力。这两种方法完全可以在同一应用程序中共存并相互补充。但是，即使对于需要最大灵活性的应用程序，仍然建议 JSON 文档具有某种固定的结构。该结构通常是不强制的（尽管可以声明性地强制执行一些业务规则），但具有可预测的结构使得更容易编写可以有效地总结表中一组 “文档”（数据）的查询。

当 JSON 数据存储在表中时，它与任何其他数据类型一样，都受并发控制的考虑。虽然存储大型文档是可行的，但请记住，任何更新都会在整行上获取行级锁。考虑限制 JSON 文档的大小在可管理的范围内，以减少更新事务之间的锁争用。理想情况下，每个 JSON 文档应代表一个原子数据，业务规则规定该数据不能合理地进一步细分为可以独立修改的更小的数据。

测试包含是 jsonb 的一项重要功能。对于 json 类型没有类似的工具集。包含性测试判断一个 jsonb 文档是否包含另一个文档。以下示例返回 true，除非另有说明

-- Simple scalar/primitive values contain only the identical value:
SELECT '"foo"'::jsonb @> '"foo"'::jsonb;

-- The array on the right side is contained within the one on the left:
SELECT '[1, 2, 3]'::jsonb @> '[1, 3]'::jsonb;

-- Order of array elements is not significant, so this is also true:
SELECT '[1, 2, 3]'::jsonb @> '[3, 1]'::jsonb;

-- Duplicate array elements don't matter either:
SELECT '[1, 2, 3]'::jsonb @> '[1, 2, 2]'::jsonb;

-- The object with a single pair on the right side is contained
-- within the object on the left side:
SELECT '{"product": "PostgreSQL", "version": 9.4, "jsonb": true}'::jsonb @> '{"version": 9.4}'::jsonb;

-- The array on the right side is not considered contained within the
-- array on the left, even though a similar array is nested within it:
SELECT '[1, 2, [1, 3]]'::jsonb @> '[1, 3]'::jsonb;  -- yields false

-- But with a layer of nesting, it is contained:
SELECT '[1, 2, [1, 3]]'::jsonb @> '[[1, 3]]'::jsonb;

-- Similarly, containment is not reported here:
SELECT '{"foo": {"bar": "baz"}}'::jsonb @> '{"bar": "baz"}'::jsonb;  -- yields false

-- A top-level key and an empty object is contained:
SELECT '{"foo": {"bar": "baz"}}'::jsonb @> '{"foo": {}}'::jsonb;

一般原则是，被包含的对象必须在结构和数据内容上与包含对象匹配，可能在从包含对象中丢弃一些不匹配的数组元素或对象键/值对之后。但请记住，在进行包含匹配时，数组元素的顺序并不重要，并且重复的数组元素实际上只被考虑一次。

作为结构必须匹配的一般原则的一个特例，数组可能包含一个原始值

-- This array contains the primitive string value:
SELECT '["foo", "bar"]'::jsonb @> '"bar"'::jsonb;

-- This exception is not reciprocal -- non-containment is reported here:
SELECT '"bar"'::jsonb @> '["bar"]'::jsonb;  -- yields false

jsonb 还有一个存在运算符，它是包含性主题的一种变体：它测试一个字符串（以 text 值给出）是否作为对象键或数组元素出现在 jsonb 值的顶层。以下示例返回 true，除非另有说明

-- String exists as array element:
SELECT '["foo", "bar", "baz"]'::jsonb ? 'bar';

-- String exists as object key:
SELECT '{"foo": "bar"}'::jsonb ? 'foo';

-- Object values are not considered:
SELECT '{"foo": "bar"}'::jsonb ? 'bar';  -- yields false

-- As with containment, existence must match at the top level:
SELECT '{"foo": {"bar": "baz"}}'::jsonb ? 'bar'; -- yields false

-- A string is considered to exist if it matches a primitive JSON string:
SELECT '"foo"'::jsonb ? 'foo';

当涉及许多键或元素时，JSON 对象比数组更适合测试包含或存在，因为与数组不同，它们在内部针对搜索进行了优化，不需要线性搜索。

SELECT doc->'site_name' FROM websites
  WHERE doc @> '{"tags":[{"term":"paris"}, {"term":"food"}]}';

SELECT doc->'site_name' FROM websites
  WHERE doc->'tags' @> '[{"term":"paris"}, {"term":"food"}]';

各种包含和存在运算符，以及所有其他 JSON 运算符和函数，都记录在 第 9.16 节 中。

GIN 索引可用于有效地搜索大量 jsonb 文档（数据）中出现的键或键/值对。提供了两个 GIN “运算符类”，它们提供不同的性能和灵活性权衡。

jsonb 的默认 GIN 运算符类支持具有键存在运算符 ?、?| 和 ?&、包含运算符 @> 以及 jsonpath 匹配运算符 @? 和 @@ 的查询。（有关这些运算符实现的语义的详细信息，请参阅 表 9.46。）使用此运算符类创建索引的示例如下

CREATE INDEX idxgin ON api USING GIN (jdoc);

非默认 GIN 运算符类 jsonb_path_ops 不支持键存在运算符，但它支持 @>、@? 和 @@。使用此运算符类创建索引的示例如下

CREATE INDEX idxginp ON api USING GIN (jdoc jsonb_path_ops);

考虑一个表的示例，该表存储从第三方 Web 服务检索的 JSON 文档，并带有已记录的架构定义。一个典型的文档是

{
    "guid": "9c36adc1-7fb5-4d5b-83b4-90356a46061a",
    "name": "Angela Barton",
    "is_active": true,
    "company": "Magnafone",
    "address": "178 Howard Place, Gulf, Washington, 702",
    "registered": "2009-11-07T08:53:22 +08:00",
    "latitude": 19.793713,
    "longitude": 86.513373,
    "tags": [
        "enim",
        "aliquip",
        "qui"
    ]
}

我们将这些文档存储在名为 api 的表中，该表在名为 jdoc 的 jsonb 列中。如果在该列上创建 GIN 索引，则类似以下查询可以使用该索引

-- Find documents in which the key "company" has value "Magnafone"
SELECT jdoc->'guid', jdoc->'name' FROM api WHERE jdoc @> '{"company": "Magnafone"}';

但是，索引不能用于类似以下查询，因为虽然运算符 ? 是可索引的，但它没有直接应用于索引列 jdoc

-- Find documents in which the key "tags" contains key or array element "qui"
SELECT jdoc->'guid', jdoc->'name' FROM api WHERE jdoc -> 'tags' ? 'qui';

尽管如此，通过适当使用表达式索引，上述查询可以使用索引。如果查询 "tags" 键中的特定项是常见的，则定义如下索引可能是有价值的

CREATE INDEX idxgintags ON api USING GIN ((jdoc -> 'tags'));

现在，WHERE 子句 jdoc -> 'tags' ? 'qui' 将被识别为对索引表达式 jdoc -> 'tags' 应用了可索引运算符 ?。（有关表达式索引的更多信息，请参见 第 11.7 节。）

查询的另一种方法是利用包含性，例如

-- Find documents in which the key "tags" contains array element "qui"
SELECT jdoc->'guid', jdoc->'name' FROM api WHERE jdoc @> '{"tags": ["qui"]}';

在 jdoc 列上的简单 GIN 索引可以支持此查询。但请注意，这样的索引将存储 jdoc 列中每个键和值的副本，而上一个示例的表达式索引仅存储在 tags 键下找到的数据。虽然简单索引方法更灵活（因为它支持对任何键的查询），但有针对性的表达式索引可能比简单索引更小、搜索速度更快。

GIN 索引还支持 @? 和 @@ 运算符，它们执行 jsonpath 匹配。示例如下

SELECT jdoc->'guid', jdoc->'name' FROM api WHERE jdoc @? '$.tags[*] ? (@ == "qui")';

SELECT jdoc->'guid', jdoc->'name' FROM api WHERE jdoc @@ '$.tags[*] == "qui"';

对于这些运算符，GIN 索引从 jsonpath 模式中提取 accessors_chain == constant 形式的子句，并根据这些子句中提到的键和值执行索引搜索。访问器链可能包括 .key、[*] 和 [index] 访问器。jsonb_ops 运算符类还支持 .* 和 .** 访问器，但 jsonb_path_ops 运算符类不支持。

虽然 jsonb_path_ops 运算符类仅支持使用 @>、@? 和 @@ 运算符的查询，但它比默认运算符类 jsonb_ops 具有显著的性能优势。jsonb_path_ops 索引通常比同一数据的 jsonb_ops 索引小得多，并且搜索的特异性更好，尤其是在查询包含数据中频繁出现的键时。因此，搜索操作的性能通常优于默认运算符类。

jsonb_ops 和 jsonb_path_ops GIN 索引之间的技术差异在于，前者为数据中的每个键和值创建独立的索引项，而后者仅为数据中的每个值创建索引项。^[7] 基本上，每个 jsonb_path_ops 索引项都是值和通向该值的键的哈希值；例如，要索引 {"foo": {"bar": "baz"}}，将创建一个包含 foo、bar 和 baz 这三个值的哈希值的单个索引项。因此，查找此结构的包含查询将导致非常具体的索引搜索；但是，完全无法找出 foo 是否作为键出现。另一方面，jsonb_ops 索引将创建三个索引项，分别表示 foo、bar 和 baz；然后，为了执行包含查询，它将查找包含所有这三个项的行。虽然 GIN 索引可以非常有效地执行这种 AND 搜索，但它仍然不如等效的 jsonb_path_ops 搜索那么具体和慢，尤其是在包含三个索引项中的任何一个项的行数非常多的情况下。

jsonb_path_ops 方法的一个缺点是，它不会为不包含任何值的 JSON 结构（例如 {"a": {}}）生成索引条目。如果请求搜索包含此类结构的文档，则需要进行完整的索引扫描，这非常慢。因此，jsonb_path_ops 不适合经常执行此类搜索的应用程序。

jsonb 还支持 btree 和 hash 索引。这些通常仅在检查完整 JSON 文档的相等性很重要时才有用。jsonb 数据的 btree 排序很少引起人们的极大兴趣，但为了完整起见，它是

Object > Array > Boolean > Number > String > Null

Object with n pairs > object with n - 1 pairs

Array with n elements > array with n - 1 elements

具有相等对数的对象按以下顺序进行比较

key-1, value-1, key-2 ...

请注意，对象键是按其存储顺序进行比较的；特别是，由于较短的键存储在较长的键之前，这可能会导致一些不直观的结果，例如

{ "aa": 1, "c": 1} > {"b": 1, "d": 1}

同样，具有相同元素数的数组按以下顺序进行比较

element-1, element-2 ...

原始 JSON 值使用与底层 PostgreSQL 数据类型相同的比较规则进行比较。字符串使用默认数据库排序规则进行比较。

jsonb 数据类型支持数组样式的下标表达式来提取和修改元素。可以通过链接下标表达式来指示嵌套值，遵循与 jsonb_set 函数中的 path 参数相同的规则。如果 jsonb 值是数组，则数字下标从零开始，负整数从数组的最后一个元素向后计数。不支持切片表达式。下标表达式的结果始终是 jsonb 数据类型。

UPDATE 语句可以使用 SET 子句中的下标来修改 jsonb 值。下标路径必须对于所有受影响的值在其存在的范围内可遍历。例如，如果每个 val、val['a'] 和 val['a']['b'] 都是对象，则可以一直遍历路径 val['a']['b']['c'] 到 c。如果未定义任何 val['a'] 或 val['a']['b']，则将创建为空对象并根据需要填充。但是，如果任何 val 本身或其中间值之一被定义为非对象（例如字符串、数字或 jsonb null），则无法继续遍历，因此会引发错误并中止事务。

下标语法的示例

-- Extract object value by key
SELECT ('{"a": 1}'::jsonb)['a'];

-- Extract nested object value by key path
SELECT ('{"a": {"b": {"c": 1}}}'::jsonb)['a']['b']['c'];

-- Extract array element by index
SELECT ('[1, "2", null]'::jsonb)[1];

-- Update object value by key. Note the quotes around '1': the assigned
-- value must be of the jsonb type as well
UPDATE table_name SET jsonb_field['key'] = '1';

-- This will raise an error if any record's jsonb_field['a']['b'] is something
-- other than an object. For example, the value {"a": 1} has a numeric value
-- of the key 'a'.
UPDATE table_name SET jsonb_field['a']['b']['c'] = '1';

-- Filter records using a WHERE clause with subscripting. Since the result of
-- subscripting is jsonb, the value we compare it against must also be jsonb.
-- The double quotes make "value" also a valid jsonb string.
SELECT * FROM table_name WHERE jsonb_field['key'] = '"value"';

通过下标进行的 jsonb 赋值与 jsonb_set 处理一些边缘情况的方式不同。当源 jsonb 值为 NULL 时，通过下标进行的赋值将如同它是由下标键暗示的类型的空 JSON 值（对象或数组）一样进行

-- Where jsonb_field was NULL, it is now {"a": 1}
UPDATE table_name SET jsonb_field['a'] = '1';

-- Where jsonb_field was NULL, it is now [1]
UPDATE table_name SET jsonb_field[0] = '1';

如果为包含太少元素的数组指定索引，则将追加 NULL 元素，直到可以访问该索引并设置该值。

-- Where jsonb_field was [], it is now [null, null, 2];
-- where jsonb_field was [0], it is now [0, null, 2]
UPDATE table_name SET jsonb_field[2] = '2';

一个 jsonb 值会接受对不存在的下标路径的赋值，只要最后一个要遍历的现有元素是对象或数组，这由相应的下标（路径中最后一个下标指示的元素不会被遍历，并且可以是任何类型）暗示。将创建嵌套的数组和对象结构，并且在前一种情况下，会按照下标路径的规定用 null 填充，直到可以放置赋值的值。

-- Where jsonb_field was {}, it is now {"a": [{"b": 1}]}
UPDATE table_name SET jsonb_field['a'][0]['b'] = '1';

-- Where jsonb_field was [], it is now [null, {"a": 1}]
UPDATE table_name SET jsonb_field[1]['a'] = '1';

还有一些额外的扩展，可以为不同的过程语言实现 jsonb 类型的转换。

PL/Perl 的扩展名为 jsonb_plperl 和 jsonb_plperlu。 如果使用它们， jsonb 值会根据需要映射到 Perl 数组、哈希和标量。

PL/Python 的扩展名为 jsonb_plpython3u。 如果使用它， jsonb 值会根据需要映射到 Python 字典、列表和标量。

在这些扩展中，jsonb_plperl 被认为是“可信的”，也就是说，具有当前数据库 CREATE 权限的非超级用户可以安装它。其余的都需要超级用户权限才能安装。

jsonpath 类型实现了在 PostgreSQL 中对 SQL/JSON 路径语言的支持，以高效地查询 JSON 数据。它提供了已解析的 SQL/JSON 路径表达式的二进制表示，该表达式指定路径引擎要从 JSON 数据中检索的项目，以便使用 SQL/JSON 查询函数进行进一步处理。

SQL/JSON 路径谓词和运算符的语义通常遵循 SQL。同时，为了提供一种自然的方式来处理 JSON 数据，SQL/JSON 路径语法使用了一些 JavaScript 约定。

SQL/JSON 路径表达式中的数值字面量遵循 JavaScript 规则，这在一些细节上与 SQL 和 JSON 不同。例如，SQL/JSON 路径允许 .1 和 1.，但在 JSON 中无效。支持非十进制整数文字和下划线分隔符，例如 1_000_000、0x1EEE_FFFF、0o273、0b100101。在 SQL/JSON 路径中（以及在 JavaScript 中，但不在 SQL 中），进制前缀后不能直接跟下划线分隔符。

SQL/JSON 路径表达式通常在 SQL 查询中写为 SQL 字符串字面量，因此必须用单引号括起来，并且值中需要的任何单引号都必须加倍（请参阅第 4.1.2.1 节）。某些形式的路径表达式需要在其中包含字符串字面量。这些嵌入的字符串字面量遵循 JavaScript/ECMAScript 约定：它们必须用双引号括起来，并且可以在其中使用反斜杠转义来表示其他难以输入的字符。特别是，在嵌入的字符串字面量中编写双引号的方式是 \"，要编写反斜杠本身，必须写成 \\。其他特殊的反斜杠序列包括 JavaScript 字符串中识别的序列：\b、\f、\n、\r、\t、\v，用于各种 ASCII 控制字符，\xNN 用于仅用两个十六进制数字编写的字符代码，\uNNNN 用于由其 4 位十六进制代码点标识的 Unicode 字符，以及 \u{N...} 用于用 1 到 6 位十六进制数字编写的 Unicode 字符代码点。

路径表达式由一系列路径元素组成，可以是以下任何一种：

有关将 jsonpath 表达式与 SQL/JSON 查询函数一起使用的详细信息，请参阅第 9.16.2 节。