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 文档的相等性很重要时才有用。btree 用于 jsonb 数据的排序很少引起人们的兴趣，但为了完整起见，它是

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 ...

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

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 节。