json

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 文档中是否包含另一个文档。除特别说明外，下面这些示例都返回真：

-- 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 值的顶层作为对象键或数组元素出现。除特别说明外，下面这些示例都返回真：

-- 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 索引项都是该值连同通向该值的键一起计算出的 hash。例如，要索引 {"foo": {"bar": "baz"}}，会创建一个单独的索引项，其 hash 值中同时纳入 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

其中有一个例外：出于历史原因，顶层空数组的排序小于 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、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 数组、hash 和标量。

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 字符码点。

路径表达式由一系列路径元素构成，这些元素可以是下列任意一种：

关于在 SQL/JSON 查询函数中使用 jsonpath 表达式的细节，见第 9.16.2 节。