Greenplum 对JSON的支持

Greenplum 对JSON的支持

源文章:http://www.postgresqltutorial.com/postgresql-json/

Greenplum 对JSON的支持 1

1 JSON概述 1

2 JSON常用运算符与函数 2

2.1 JSON常用运算符 2

2.2 JSON常用的创建函数 3

2.3 JSON处理函数 4

3 JSON 运算符常用实例 5

3.1 单组JSON解析 5

3.2 多组JSON解析 5

3.3 复杂的JSON解析 6

3.3.1 多个JSON子集的解析 6

3.3.2 获取JSON子集的数据 6

3.3.3 获取一个JSON集合的子元素 6

4 JSON 创建函数的使用 7

4.1 创建int类型的JSON格式数据 7

4.2 把行的数据转化为JSON类型的数据 7

5 JSON处理函数的使用 8

5.1 获取JSON中的数据 8

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

5.3 获取JSON数据中的KEY的值 9

6 查询JSON数据的方式 9

6.1 创建支持JSON数据的表 9

6.1.1 创建表的SQL 9

6.1.2 插入数据SQL 10

6.1.3 获取JSON数据的KEY值 10

6.2 获取JSON结构中的数据 11

6.3 按照条件查询数据 12

6.4 集合函数查询JSON数据 13

6.5 使用默认的函数查找数据 14

6.5.1 JSON_EACH 函数的使用 14

6.5.2 JSON_OBJECT_KEYS 函数的使用 14

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

2 JSON常用运算符与函数

2.1 JSON常用运算符

Operator

Right Operand Type

Description

Example

Example Result

->

int

Get JSON array element (indexed from zero).

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

{"c":"baz"}

->

text

Get JSON object field by key.

'{"a": {"b":"foo"}}'::json->'a'

{"b":"foo"}

->>

int

Get JSON array element as text.

'[1,2,3]'::json->>2

3

->>

text

Get JSON object field as text.

'{"a":1,"b":2}'::json->>'b'

2

#>

text[]

Get JSON object at specified path.

'{"a": {"b":{"c": "foo"}}}'::json#>'{a,b}'

{"c": "foo"}

#>>

text[]

Get JSON object at specified path as text.

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

3

2.2 JSON常用的创建函数

array_to_json(anyarray [, pretty_bool])

row_to_json(record [, pretty_bool])

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)

3 JSON 运算符常用实例

3.1 单组JSON解析

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

3.2 多组JSON解析

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

3.3 复杂的JSON解析

3.3.1 多个JSON子集的解析

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

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

3.3.2 获取JSON子集的数据

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

注意#>'{a,b}的使用,表示一层一层的查询

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

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

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

4 JSON 创建函数的使用

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

select array_to_json('{{1,5},{99,100}}'::int[]) as jsondata;

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

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

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

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

5 JSON处理函数的使用

5.1 获取JSON中的数据

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

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

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

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

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

5.3 获取JSON数据中的KEY的值

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

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

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

6 查询JSON数据的方式

6.1 创建支持JSON数据的表

6.1.1 创建表的SQL

创建带有主键的表

CREATE TABLE test_json (

ID serial NOT NULL PRIMARY KEY,

info json NOT NULL

);

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.1.3 获取JSON数据的KEY值

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

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

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

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

6.2 获取JSON结构中的数据

SELECT

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

FROM

test_json

ORDER BY

product;

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

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.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.5 使用默认的函数查找数据

6.5.1 JSON_EACH 函数的使用

SELECT

json_each(info)

FROM

test_json;

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

6.5.2 JSON_OBJECT_KEYS 函数的使用

SELECT

json_object_keys (info->'items') as jsondata

FROM

test_json;

原创声明,本文系作者授权云+社区发表,未经许可,不得转载。

如有侵权,请联系 yunjia_community@tencent.com 删除。

编辑于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏偏前端工程师的驿站

MyBatis魔法堂:即学即用篇

一、前言                                          本篇内容以理解MyBatis的基本用法和快速在项目中实践为目的,...

2176
来自专栏Ryan Miao

mysql插入日期 vs oracle插入日期

今天做oracle日期插入的时候突然开始疑惑日期是如何插入的。 用框架久了,反而不自己做简单的工作了。比如插入。 通常,新建一个表对象,然后绑定数据,前端for...

3069
来自专栏耕耘实录

Linux三大剑客之awk

版权声明:本文为耕耘实录原创文章,各大自媒体平台同步更新。欢迎转载,转载请注明出处,谢谢

1454
来自专栏GreenLeaves

C# 文件读写系列三

1、读写文本文件 在C# 文件读写系列二中列举了相当多的读写文本文件的方法,大致有以下几种: (1)、通过静态类File的静态方法来进行文本文件的读写,主要有R...

23110
来自专栏Golang语言社区

go语言的sql包原理与用法分析

go的sql包是在pkg/database中,里面的两个包sql和sql/driver可以一起看。建议看这个两个包之前可以先看看sql文件夹下的doc.txt。...

2984
来自专栏机器学习从入门到成神

Java知识点总结

版权声明:本文为博主原创文章,未经博主允许不得转载。 https://blog.csdn.net/sinat_35512245/articl...

1461
来自专栏程序员叨叨叨

【PHP】Propel的使用,看这一篇就够了

本文为学习Propel框架使用的笔记,默认已经安装好Propel环境,若有读者不知如何安装Propel,可参考《听说你PHP配置Composer遇到了一些困境》...

3125
来自专栏用户2442861的专栏

[疯狂Java]JDBC:PreparedStatement预编译执行SQL语句

    1) SQL语句和编程语言一样,仅仅就会普通的文本字符串,首先数据库引擎无法识别这种文本字符串,而底层的CPU更不理解这些文本字符串(只懂二进制机器指...

2442
来自专栏数据结构与算法

SPOJ1043 GSS1(线段树)

注意查询的时候不能按照以前的方式写,因为不知道变量的下界,最稳妥的办法就是判三种情况

951
来自专栏IT笔记

Hibernate合并查询结果集为实体类

用过mybatis的小伙伴可能都知道,我们可以查询两个表的部分字段合并为一个实体。然而用了Hibernate这么久了,居然还不知道也有此神器。 ? hibern...

4006

扫码关注云+社区