使用JSON数据

Greenplum数据库按照RFC 7159文档的说明支持存储JSON（JavaScript Object Notation）数据的json数据类型，并且根据JSON规则强制数据有效性。也有一些用于json数据的JSON相关的函数和操作符。请见JSON函数和操作符。

这一节包含下列主题：

上级主题： 查询数据

关于JSON数据

当Greenplum数据库把数据存为json数据类型时，会存储一份输入文本的准确拷贝并且每一次执行时会由JSON处理函数重新解析该数据。

  • 记号之间没有语义意义的空格被保留，JSON对象内的键的顺序也被保留。
  • 即便JSON对象含有重复键，所有的键/值对也会被保留。对于重复键，JSON处理函数把其中的最后一个值当作有效值。

Greenplum数据库对每一个数据库只允许一种字符集编码。除非数据库编码为UTF8，json类型不可能严格地符合JSON说明。尝试包括数据库编码中无法表示的字符将会失败。允许可在数据库编码中表示但不在UTF8中的字符。

RFC 7159文档允许JSON字符串包含表示为\uXXXX的Unicode转义序列。对于json类型，Greenplum数据库输入函数允许Unicode转义而不管数据库是何种编码，并且只会为语法正确性（一个\u后面跟着四个16进制数）。

注意： JSON函数和操作符中描述的很多JSON处理函数会把Unicode转义转换成常规字符。对于无法在数据库编码中表达的字符，这些函数会抛出一个错误。应该尽可能避免在JSON中混合Unicode转义和非UTF8数据库编码。

JSON输入和输出语法

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

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

  1. -- 简单标量/简单值
  2. -- 简单值可以是数字、带引号的字符串、true、false或者null
  3. SELECT '5'::json;
  5. -- 零个或者更多个元素的数组（元素类型可以不同）
  6. SELECT '[1, 2, "foo", null]'::json;
  8. -- 含有键/值对的对象
  9. -- 注意对象的键必须总是带引号的字符串
  10. SELECT '{"bar": "baz", "balance": 7.77, "active": false}'::json;
  12. -- 数组和对象可以任意嵌套
  13. SELECT '{"foo": [true, "bar"], "tags": {"a": 1, "b": null}}'::json;

设计JSON文档

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

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

JSON函数和操作符

创建和操纵JSON数据的内建函数和操作符。

注意： 对于json值，即便一个JSON对象含有重复键，所有的键/值对也会被保留。对于重复键，JSON处理函数会把最后一个值当成有效值。

JSON操作符

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

表 1. json操作符
操作符右操作数类型描述例子例子结果
->int获得JSON数组元素（索引从零开始）。‘[{“a”:”foo”},{“b”:”bar”},{“c”:”baz”}]’::json->2{“c”:”baz”}
->text根据键获得JSON对象的域。‘{“a”: {“b”:”foo”}}’::json->’a’{“b”:”foo”}
->>int获得JSON数组元素的文本形式。‘[1,2,3]’::json->>23
->>text获得JSON对象域的文本形式。‘{“a”:1,”b”:2}’::json->>’b’2
#>text[]获得在指定路径上的JSON对象。‘{“a”: {“b”:{“c”: “foo”}}}’::json#>’{a,b}‘{“c”: “foo”}
#>>text[]获得在指定路径上的JSON对象的文本形式。‘{“a”:[1,2,3],”b”:[4,5,6]}’::json#>>’{a,2}’3

JSON创建函数

这个表描述创建json值的函数。

表 2. JSON创建函数
函数描述例子例子结果
to_json(anyelement)返回该值作为一个合法的JSON对象。数组和组合会被递归处理并且转换成数组和对象。如果输入包含一个从该类型到json的造型，会使用该cast函数来执行转换，否则将会产生一个JSON标量值。对于任何非数字、布尔值或空值的标量类型，会使用其文本表示，并且加上适当的引号和转义让它变成一个合法的JSON字符串。to_json(‘Fred said “Hi.”‘::text)“Fred said \”Hi.\””
array_to_json(anyarray [, pretty_bool])返回该数组为一个JSON数组。一个Greenplum数据库多维数组会变成一个JSON数组的数组。

如果pretty_bool为true，在第一维元素之间会增加换行。

array_to_json(‘{{1,5},{99,100}}’::int[])[[1,5],[99,100]]
row_to_json(record [, pretty_bool])返回该行为一个JSON对象。

如果pretty_bool为true，在第一级别元素之间会增加换行。

row_to_json(row(1,’foo’)){“f1”:1,”f2”:”foo”}

注意： array_to_json和row_to_json具有与to_json相同的行为，只不过前两者提供了优质打印选项。针对to_json所描述的行为也适用于由其他JSON创建函数转换的个体值。

JSON处理函数

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

表 3. JSON处理函数
函数返回类型描述例子例子结果
json_each(json)setof key text, value json

setof key text, value jsonb

把最外层的JSON对象展开成键/值对的集合。select from json_each(‘{“a”:”foo”, “b”:”bar”}’)
  1.  key | value
  2. ——-+———-
  3.  a   | “foo”
  4.  b   | “bar”
json_each_text(json)setof key text, value text把最外层的JSON对象展开成键/值对的集合。返回值的类型是text。select from json_each_text(‘{“a”:”foo”, “b”:”bar”}’)
  1.  key | value
  2. ——-+———-
  3.  a   | foo
  4.  b   | bar
json_extract_path(from_json json, VARIADIC path_elems text[])json返回path_elems指定的JSON值。等效于#>操作符。json_extract_path(‘{“f2”:{“f3”:1},”f4”:{“f5”:99,”f6”:”foo”}}’,’f4’){“f5”:99,”f6”:”foo”}

json_extract_path_text(from_json json, VARIADIC path_elems text[])

text返回path_elems指定的JSON值为文本。等效于#>>操作符。json_extract_path_text(‘{“f2”:{“f3”:1},”f4”:{“f5”:99,”f6”:”foo”}}’,’f4’, ‘f6’)foo
json_object_keys(json)setof text返回最外层JSON对象中的键集合。json_object_keys(‘{“f1”:”abc”,”f2”:{“f3”:”a”, “f4”:”b”}}’)
  1.  json_object_keys
  2. —————————
  3.  f1
  4.  f2
json_populate_record(base anyelement, from_json json)anyelement把Expands the object in from_json中的对象展开成一行，其中的列匹配由base定义的记录类型。请见注解。select from json_populate_record(null::myrowtype, ‘{“a”:1,”b”:2}’)
  1.  a | b
  2. —-+—-
  3.  1 | 2
json_populate_recordset(base anyelement, from_json json)setof anyelement将from_json中最外层的对象数组展开成一个行集合，其中的列匹配由base定义的记录类型。请见注解。select from json_populate_recordset(null::myrowtype, ‘[{“a”:1,”b”:2},{“a”:3,”b”:4}]’)
  1.  a | b
  2. —-+—-
  3.  1 | 2
  4.  3 | 4
json_array_elements(json)setof json将一个JSON数组展开成JSON值的一个集合。select * from json_array_elements(‘[1,true, [2,false]]’)
  1.    value
  2. —————-
  3.  1
  4.  true
  5.  [2,false]

注意： 很多这些函数和操作符把JSON字符串中的Unicode转义变换成普通字符。对于无法在数据库编码中标识的字符，这些函数会抛出一个错误。

对于json_populate_record和json_populate_recordset，来自JSON的类型强制是尽力而为并且对于一些类型可能不会得到想要的值。在目标行类型中，JSON的键匹配相同的列名。不出现在目标行类型中的JSON域会被从输出中省略，并且不匹配任何JSON域的目标列会返回NULL。