使用JSON数据

Greenplum数据库支持json和jsonb数据类型来存储JSON (JavaScript Object Notation)数据。

Greenplum数据库按照文档RFC 7159 支持JSON,并且根据JSON规则强制数据有效性。也有一些用于json and jsonb数据类型的JSON相关的函数和操作符。参见JSON函数和操作符.

这一节包含下列主题：

• 关于JSON数据
• JSON输入和输出语法
• 设计JSON文档
• jsonb容器与存在
• jsonb索引
• JSON函数和操作符

Parent topic: 查询数据

关于JSON数据

Greenplum数据库支持两种JSON数据类型: json and jsonb. 他们输入值基本一致，主要区别之一是效率。

• The json数据类型存储输入副本. 这要求JSON处理函数每次执行时处理json 数据。json数据类型不会修改输入文本。
  • 记号之间没有语义意义的空格被保留，JSON对象内的键的顺序也被保留。
  • 即便JSON对象含有重复键，所有的键/值对也会被保留。对于重复键，JSON处理函数把其中的最后一个值当作有效值。

• jsonb数据类型存储输入文本的二进制格式。输入数据的转换开销使jsonb稍慢于 json数据类型. 但是, json处理函数明显更快，因为解析jsonb数据不是必须的。 jsonb数据类型修改输入文本。

  • 空格不保留
  • 对象内的键的顺序不保留
  • 重复键对象不保留，如果输入包含重复键，仅保留最后一个值。

  jsonb数据类型支持索引。参考 jsonb索引.

一般来说，JSON数据应该存储为jsonb数据类型，除非特殊需要，例如关于对象键顺序的遗留假设。

关于JSON数据中的Unicode字符

The RFC 7159文档允许JSON字符串包含表示为\uXXXX的\uXXXXUnicode转义序列。不过, Greenplum对每一个数据库只允许一种字符集编码。 除非数据库编码为UTF8，json类型不可能严格地符合JSON说明。尝试包括数据库编码中无法表示的字符将会失败。允许可在数据库编码中表示但不在UTF8中的字符。

• json数据类型的greenplum数据库输入函数允许unicode转义，而无论是什么数据库编码，只检查unicode转义的语法正确性（a \u后跟四个十六进制数字）。
• jsonb数据类型的greenplum数据库输入函数更严格。它不允许非ASCII字符（U+007F以上的字符）使用Unicode转义，除非数据库编码是UTF8。它还拒绝\u0000，它不能在greenplum数据库text类型中表示，并且它要求使用unicode代理项对指定unicode基本多语言平面之外的字符是正确的。有效的Unicode转义符（除了\u0000）转换为等效的ASCII或UTF8字符进行存储；这包括将代理项对折叠为单个字符。

Note: JSON函数和运算符中描述的许多JSON处理函数将Unicode转义转换为常规字符。函数对数据库编码中无法表示的字符抛出错误。如果可能的话，应该避免将JSON中的Unicode转义与非UTF8数据库编码混合在一起。

将JSON数据类型映射到Greenplum数据类型

When converting JSON text input into jsonb data, the primitive data types described by RFC 7159 are effectively mapped onto native Greenplum Database data types, as shown in the following table.

对于有效的jsonb 数据的构成有一些次要的约束，这些约束既不适用于json 数据类型，也不适用于抽象的JSON，对应于基础数据类型所能表示的限制。值得注意的是，当将数据转换为jsonb数据类型时，超出Greenplum数据库numeric数据类型范围的数字将被拒绝，而json数据类型不会拒绝这些数字。

RFC 7159允许这种实施定义的限制。然而，在实践中，这些问题可能会出现在其他实现中，因为通常将JSON原始类型 number表示为IEEE 754双精度浮点（RFC7159明确预测并允许）。

当使用JSON作为与其他系统的交换格式时，请注意与Greenplum数据库最初存储的数据相比，数字精度可能会降低。

另外，正如上表中所指出的，对于JSON原语类型的输入格式，存在一些小的限制，这些限制不适用于相应的Greenplum数据库数据类型。

JSON输入和输出语法

json数据类型的输入和输出语法如RFC 7159中所述。

下列都是合法的json表达式：

-- 简单标量/原始值
-- 原始值值可以是数字、带引号的字符串、true、false或者null SELECT '5'::json;
-- 零个或者更多个元素的数组（元素类型可以不同）
          SELECT '[1, 2, "foo", null]'::json;
-- 含有键/值对的对象
-- 注意对象的键必须总是带引号的字符串
SELECT '{"bar": "baz", "balance": 7.77, "active": false}'::json;
-- 数组和对象可以任意嵌套
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数据类型，将根据基础数字类型的行为打印数字。在实践中，这意味着使用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文档

将数据表示为JSON比传统关系数据模型要更灵活，在需求变化的环境中表现得尤其明显。在同一个应用中很可能两种方法会共存并且成为彼此的互补。不过，即便是对于要求最大灵活性的应用中，我们仍然推荐JSON有些许固定的结构。这种结构是非强制的（尽管可能会强制一些业务规则），但是如果有可预测的结构会让编写有效汇总表中一组“文档”（数据）的查询更容易。

在表中存储时，JSON数据服从和其他任何数据类型一样的并发控制考虑。尽管存储大型文档格式可行的，但要记住任何更新都要求整个行上的一个行级锁。为了降低更新事务之间的锁竞争，请考虑限制JSON文档为一个可管理的尺寸。理想上，每个JSON文档应该表示业务规则规定的一个原子数据，并且不能进一步地被分解为更小的可以独立修改的数据。

jsonb容器与存在

测试容器是jsonb的一项重要功能。 json类型没有并行的设施集。容器测试一个jsonb 文档中是否包含了另一个jsonb 文档。这些例子返回true，除非另有标注：

--简单标量/原始数值仅包含相同的值：
SELECT '"foo"'::jsonb @> '"foo"'::jsonb;
-- 右边的数组包含在左边的数组中：
SELECT '[1, 2, 3]'::jsonb @> '[1, 3]'::jsonb;
--数组元素的顺序并不重要，因此这也是正确的：
SELECT '[1, 2, 3]'::jsonb @> '[3, 1]'::jsonb;
-- 重复的数组元素也不重要：
SELECT '[1, 2, 3]'::jsonb @> '[1, 2, 2]'::jsonb;
-- 右侧只有一对的对象包含在左侧的对象中:
SELECT '{"product": "Greenplum", "version": "6.0.0", "jsonb":true}'::jsonb @> '{"version":"6.0.0"}'::jsonb;
--右侧的数组被认为不包含在左侧的数组中，即使其中嵌套了类似的数组：
SELECT '[1, 2, [1, 3]]'::jsonb @> '[1, 3]'::jsonb;  -- yields false
--但是，通过一层嵌套，它包含：
SELECT '[1, 2, [1, 3]]'::jsonb @> '[[1, 3]]'::jsonb;
--同样，此处未报告遏制：
SELECT '{"foo": {"bar": "baz", "zig": "zag"}}'::jsonb @> '{"bar": "baz"}'::jsonb; -- yields false
-- 但是，通过一层嵌套，它包含：
SELECT '{"foo": {"bar": "baz", "zig": "zag"}}'::jsonb @> '{"foo": {"bar": "baz"}}'::jsonb;

一般原则是，所包含的对象必须在结构和数据内容方面与包含的对象匹配，可能是在从包含的对象中丢弃一些不匹配的数组元素或对象键/值对之后。 对于容器，在进行容器匹配时，数组元素的顺序并不重要，重复的数组元素只被有效地考虑一次。

作为结构必须匹配的一般原则的例外，数组可以包含原始值：

-- 此数组包含原始字符串值：:
SELECT '["foo", "bar"]'::jsonb @> '"bar"'::jsonb;
-- 这个异常不是相互的——这里报告了非包容：:
SELECT '"bar"'::jsonb @> '["bar"]'::jsonb;  -- yields false

jsonb 还有一个existence操作符，它是容器主题的变体：它测试字符串（作为文本值给出）是否作为对象键或数组元素出现在jsonb 值的顶层。这些示例返回true，，除非另有标注：

-- 字符串作为数组元素存在：
SELECT '["foo", "bar", "baz"]'::jsonb ? 'bar';
-- 字符串作为对象键存在：
SELECT '{"foo": "bar"}'::jsonb ? 'foo';
-- 不考虑对象值：
SELECT '{"foo": "bar"}'::jsonb ? 'bar';  -- yields false
-- 与容器一样，存在必须在顶层匹配：
SELECT '{"foo": {"bar": "baz"}}'::jsonb ? 'bar'; -- yields false
-- 如果字符串与原始JSON字符串匹配，则认为该字符串存在：
SELECT '"foo"'::jsonb ? 'foo';

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

各种容器和存在操作符以及所有其他JSON操作符和函数都记录在JSON函数和操作符JSON函数和操作符中。

因为JSON容器是嵌套的，所以适当的查询可以跳过子对象的显式选择。例如，假设我们有一个包含顶级对象的doc列，其中大多数对象包含子对象数组的标记字段。此查询查找包含”term”:”paris”和”term”:”food”的子对象出现的条目，同时忽略标记数组之外的任何此类键：

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存在操作符不是嵌套的：它只在json值的顶层查找指定的键或数组元素。

jsonb索引

Greenplum数据库jsonb数据类型，支持GIN, btree,和hash索引。

• jsonb数据上的GIN索引
• jsonb数据上的树索引和哈希索引

jsonb数据上的GIN索引

可以使用GIN索引有效地搜索出现在大量jsonb文档（基准）中的键或键/值对。两个GIN操作符类，提供不同的性能和灵活性权衡。

jsonb的默认GIN操作符类支持带@>, ?, ?& 和 ?|操作符的查询。（有关这些运算符实现的语义的详细信息，请参见表 Table 3 ）。使用此操作符类创建索引的示例如下：

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"
    ]
}

JSON文档存储在jsonb列中的一个名为API的表中。如果在此列上创建了GIN索引，则以下查询可以使用该索引：

-- 查找关键词“公司”具有“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’。有关表达式索引的信息，请参阅表达式索引 表达式索引。

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

-- 查找键“tags”包含数组元素"qui"的文档
            SELECT jdoc->'guid', jdoc->'name' FROM api WHERE jdoc @> '{"tags": ["qui"]}';

jdoc列上的一个简单的GIN索引可以支持这个查询。但是，索引将在jdoc列中存储每个键和值的副本，而前一个示例的表达式索引只存储标记键下的数据。虽然简单索引方法要灵活得多（因为它支持关于任何键的查询），但是目标表达式索引可能比简单索引更小，搜索速度更快。

尽管 jsonb_path_ops类只支持带@> 运算符的查询，但它比默认的jsonb_operator类具有性能优势。对于相同的数据， jsonb_path_ops索引通常比jsonb_ops索引小得多，搜索的特殊性更好，尤其是当查询包含频繁出现在数据中的键时。因此，搜索操作通常比默认的operator类执行得更好。

jsonb_ops 和 jsonb-path-ops-gin 索引的技术区别在于前者为数据中的每个键和值创建独立的索引项，而后者仅为数据中的每个值创建索引项。

Note: 对于这个讨论，术语value包括数组元素，尽管JSON术语有时考虑数组元素不同于对象中的值。

基本上，每个jsonb_path_ops索引项都是值的散列和指向该值的键；例如，要索引{“foo”: {“bar”: “baz”}}，将创建一个索引项，将foo, bar, 和baz 中的三个都合并到散列值中。因此，查找此结构的包含查询将导致极其具体的索引搜索；但根本无法确定foo是否显示为键。另一方面，jsonb_ops 索引将分别创建三个表示foo, bar, 和baz 的索引项；然后，为了执行包含性查询，它将查找包含这三个项的行。虽然GIN索引可以相当有效地执行这样的搜索，但是它仍然比同等的jsonb_path_ops搜索更不具体和更慢，特别是如果有大量的行包含三个索引项中的任何一个。

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

jsonb数据上的树索引和哈希索引

jsonb还支持btree和hash索引。只有在检查完整JSON文档的相等性很重要时，这些方法才有用。

为完整起见，josonb 基准的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 ...

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

JSON函数和操作符

Greenplum数据库包含创建和操作JSON数据的内置函数和运算符。

• JSON操作符
• JSON创建函数
• JSON聚合函数
• JSON处理函数

Note: 对于json数据类型值，即使JSON对象包含重复的键，也会保留所有键/值对。对于重复的键，JSON处理函数将最后一个值视为可操作的值。对于JSONB数据类型，不保留重复的对象键。如果输入包含重复键，则只保留最后一个值。请参见关于JSON数据。

JSON操作符

这个表格描述了可以用于json 和 jsonb 数据类型的操作符。

Note: 对于json和jsonb数据类型，这些运算符都有并行变体。字段、元素和路径提取运算符返回的数据类型与其左侧输入（json和jsonb）相同，但指定为返回text的数据类型除外，后者将值强制为text。如果JSON输入没有与请求匹配的正确结构，则字段、元素和路径提取运算符返回NULL，而不是失败；例如，如果不存在这样的元素。

下表描述了需要jsonb数据类型作为左操作数的运算符。这些运算符中的许多都可以通过jsonb运算符类进行索引。有关jsonb包含和存在语义的完整描述，请参见jsonb容器与存在。有关如何使用这些运算符有效地索引JSONB数据的信息，请参阅jsonb索引。

下表中的标准比较运算符仅适用于 jsonb数据类型，而不适用于 json数据类型。它们遵循 jsonb索引jsonb索引中描述的B树操作的排序规则。

Note: 这个!=运算符在解析阶段转换为 <>。无法执行 !=和<>来执行不同操作的运算符。

JSON创建函数

此表描述了创建 json数据类型值的函数。（目前， jsonb没有等价的函数，但是您可以将其中一个函数的结果强制转换为 jsonb。）

Note: array_to_json和row_to_json的行为与to_json 相同，只是提供了一个打印漂亮的选项。to_json描述的行为同样适用于其他JSON创建函数转换的每个单独的值。

Note: hstorehstore模块包含从hstore转换为json的函数，因此通过json创建函数转换的hstore值将被表示为json对象，而不是原始字符串值。

JSON聚合函数

此表显示函数将记录聚合到一个JSON对象数组和一对JSON对象的值。

JSON处理函数

这个表描述处理json 和 jsonb值的函数。

许多处理函数和运算符将JSON字符串中的Unicode转义符转换为适当的单个字符。如果输入数据类型是jsonb，这不是问题，因为转换已经完成。但是，对于json数据类型输入，这可能导致抛出错误。请参见关于 关于JSON数据。

 key | value
——-+———-
 a   | “foo”
 b   | “bar”

 key | value
——-+———-
 a   | foo
 b   | bar

 json_object_keys
—————————
 f1
 f2

 a | b
—-+—-
 1 | 2

 a | b
—-+—-
 1 | 2
 3 | 4

   value
—————-
 1
 true
 [2,false]

   value
—————-
 foo
 bar

 a |    b    | d
—-+————-+—-
 1 | [1,2,3] |

 a |  b
—-+——-
 1 | foo
 2 |

Note:

1. 函数json_populate_record(), json_populate_recordset(), json_to_record() 和 json_to_recordset()的示例使用常量。 但是，典型的用法是引用FROM子句中的表，并使用它的json或jsonb列之一作为函数的参数。然后可以在查询的其他部分中引用提取的键值。例如，可以在WHERE子句和目标列表中引用该值。以这种方式提取多个值可以提高性能，而不是使用每个键操作符分别提取多个值。

   JSON键与目标行类型中相同的列名匹配。这些函数的JSON类型强制可能不会产生某些类型所需的值。目标行类型中未出现的JSON字段将从输出中省略，不匹配任何JSON字段的目标列将为空。

2. json_typeof函数NULL返回值 null不应与SQLNULL混淆。当调用json_typeof(‘null’::json)将返回null时，调用json_typeof(NULL::json)将返回SQLNULL。