Greenplum数据库使用总结-对JSON的支持

6 Greenplum对JSON的支持

6.1 JSON概述

JSON作为结构化的数据，目前越来越受到开发者的爱戴，它简单灵活易于理解。是作为储存数据的一种比较使用的一种格式，greenplum从5.0开始便很好的支持了JSON数据。

参考资料:https://hashrocket.com/blog/posts/faster-json-generation-with-postgresql#how-to

Greenplum官网介绍:https://gpdb.docs.pivotal.io/530/admin_guide/query/topics/json-data.html

6.2 JSON常用运算符与函数

6.2.1 JSON常用运算符

6.2.2 JSON常用的创建函数

array_to_json(anyarray [, pretty_bool])

row_to_json(record [, pretty_bool])

6.2.3 JSON处理函数

json_each(json)

json_each_text(json)

json_extract_path(from_json json, VARIADIC path_elems text[])

json_extract_path_text(from_json json, VARIADIC path_elems text[])

json_object_keys(json)

json_populate_record(base anyelement, from_json json)

json_populate_recordset(base anyelement, from_json json)

json_array_elements(json)

6.3 JSON运算符常用实例

6.3.1单组JSON解析

select '{"a":1}'::json ->>'a' as jsondata;

6.3.2多组JSON解析

select '{"a":1,"b":2}'::json->>'b' as jsondata;

6.3.3复杂的JSON解析

6.3.3.1多个JSON子集的解析

select '[{"a":"foo"},{"b":"bar"},{"c":"baz"}]'::json->2 as jsondata;

注意以上结果查询的坐标是从开始的，查询条件必须是索引

6.3.3.2获取JSON子集的数据

select '{"a": {"b":{"c": "foo"}}}'::json#>'' as jsondata;

注意#>'的使用，表示一层一层的查询

6.3.3.3获取一个JSON集合的子元素

select '{"a":[1,2,3],"b":[4,5,6]}'::json#>>'' as jsondata;

注意这个JSON写的格式，以及获取的顺序

6.4 JSON创建函数的使用

6.4.1创建int类型的JSON格式数据

select array_to_json('{,}'::int[]) as jsondata;

注意int数组的json数据已经把原本的格式转换了。

6.4.2把行的数据转化为JSON类型的数据

select row_to_json(row(1,2,'foo')) as jsondata;

注意查看以上的结果可以看出row是行的数据，结果中f1,f2,f3是默认的字段的名，在后面将会介绍怎样获取字段名转化为JSON。

6.5 JSON处理函数的使用

6.5.1获取JSON中的数据

select * from json_each('{"a":"foo", "b":"bar"}');

以上结果只显示出了key与value的值，如果只需要这部分的数据导出来更好。

6.5.2获取JSON中的数据(去除双引号)

select * from json_each_text('{"a":"foo", "b":"bar"}')

可以注意到与上一个比较value的值去除了双引，这个数据是比较使用的。

6.5.3获取JSON数据中的KEY的值

select * from json_object_keys('{"f1":"abc","f2":{"f3":"a", "f4":"b"}}') as jsondata;

只把数据的key的值获取出来了，注意别名的使用，必须放在数据的后面，不知道为啥?

经过测试竟然没有json_object_values的方法。

6.6 JSON查询数据的方式

6.6.1创建支持JSON数据的表

6.6.1.1创建表的SQL

创建带有主键的表

CREATE TABLE test_json (

ID serial NOT NULL PRIMARY KEY,

info json NOT NULL

);

serial可以实现自增值

6.6.1.2插入数据SQL

INSERT INTO test_json (info)

VALUES

(

'{ "customer": "John Doe", "items": {"product": "Beer","qty": 6}}'

),

(

'{ "customer": "Lily Bush", "items": {"product": "Diaper","qty": 24}}'

),

(

'{ "customer": "Josh William", "items": {"product": "Toy Car","qty": 1}}'

),

(

'{ "customer": "Mary Clark", "items": {"product": "Toy Train","qty": 2}}'

);

6.6.1.3获取JSON数据的KEY值

SELECT info -> 'customer' AS customer FROM test_json;

以上数据只把制定KEY的VALUE获取出来，注意使用->是不把双引号去掉的。

SELECT info ->> 'customer' AS customer FROM test_json;

使用->>就可以把双引去掉了。

6.6.2获取JSON结构中的数据

SELECT

info -> 'items' ->> 'product' AS product

FROM

test_json

ORDER BY

product;

SQL中可以->与->>一起使用，区别就是结果有无双引的问题。

6.6.3按照条件查询数据

SELECT

info ->> 'customer' AS customer

FROM

test_json

WHERE

info -> 'items' ->> 'product' = 'Diaper'

查询条件也可以作为解析的对象。

也可以写成以下的形式

SELECT

info ->> 'customer' AS customer,

info -> 'items' ->> 'product' AS product

FROM

test_json

WHERE

CAST (

info -> 'items' ->> 'qty' AS INTEGER

) = 2

info -> 'items' ->> 'qty' AS INTEGER是获取json集合中元素是qty的数据 转化为INTEGER,

case()是把数值转化为int4类型

6.6.4集合函数查询JSON数据

SELECT

MIN(CAST( info -> 'items' ->> 'qty' AS INTEGER)),

MAX (CAST (info -> 'items' ->> 'qty' AS INTEGER)),

SUM (CAST (info -> 'items' ->> 'qty' AS INTEGER)),

AVG (CAST (info -> 'items' ->> 'qty' AS INTEGER))

FROM

test_json

6.6.5使用默认的函数查找数据

6.6.5.1 JSON_EACH函数的使用

SELECT

json_each(info)

FROM

test_json;

json_each函数把含有key与value的数据全部取了出来，如果一行有多个key与value则会把分行显示出来。

6.6.5.2 JSON_OBJECT_KEYS函数的使用

SELECT

json_object_keys (info->'items') as jsondata

FROM

test_json;

6.6.6把查询数据转化为JSON

6.6.6.1查看原始数据

select * from test_json_date;

6.6.6.2把查询的数据转化为JSON6.6.6.2.1把字段的名字作为JSON对象

select row_to_json(test_json_date) from test_json_date;

test_json_date是表的名字，row_to_json()里面的也是表里面的名字

6.6.6.2.2使用默认的JSON字段名字

select row_to_json(row(field1, field2)) from test_json_date;

可以看出已使用默认的字段作为JSON的对象了。

或写成以下形式