使用JSON数据
Greenplum数据库按照RFC 7159文档的说明支持存储JSON(JavaScript Object Notation)数据的json
数据类型,并且根据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
表达式:
-- 简单标量/简单值
-- 简单值可以是数字、带引号的字符串、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比传统关系数据模型要更灵活,在需求变化的环境中表现得尤其明显。在同一个应用中很可能两种方法会共存并且成为彼此的互补。不过,即便是对于要求最大灵活性的应用中,我们仍然推荐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->>2 |
3 |
->> |
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"}') |
``` key | value -----+------- a | "foo" b | "bar" ``` |
json_each_text(json) |
setof key text, value text |
把最外层的JSON对象展开成键/值对的集合。返回值的类型是text 。 |
select * from json_each_text('{ "a":"foo", "b":"bar"}') |
``` key | value -----+------- a | foo 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"}}') |
json_object_keys ------------------ f1 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}') |
``` a | b ---+--- 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}]') |
``` a | b ---+--- 1 | 2 3 | 4 ``` |
json_array_elements(json) |
setof json |
将一个JSON数组展开成JSON值的一个集合。 | select * from json_array_elements('[1,true, [2,false]]') |
value ----------- 1 true [2,false] |
注意: 很多这些函数和操作符把JSON字符串中的Unicode转义变换成普通字符。对于无法在数据库编码中标识的字符,这些函数会抛出一个错误。对于json_populate_record
和json_populate_recordset
,来自JSON的类型强制是尽力而为并且对于一些类型可能不会得到想要的值。在目标行类型中,JSON的键匹配相同的列名。不出现在目标行类型中的JSON域会被从输出中省略,并且不匹配任何JSON域的目标列会返回NULL
。