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 表示法的数字将不带 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.48。）创建此运算符类的索引的示例是

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

例外情况是（由于历史原因）顶层空数组排序小于null。具有相同数量对的对象按顺序比较

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['a']['b']['c'] 可以一直遍历到 c，如果每个 val、val['a'] 和 val['a']['b'] 都是对象。如果任何 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 节。