Greenplum json类型的使用

1.建json类型字段的表

CREATE TABLE orders (
    ID serial NOT NULL PRIMARY KEY,   
    info json NOT NULL
);

2.插入json类型的数据

INSERT INTO orders (info) VALUES  
( '{ "customer": "John Doe", "items": {"product": "Beer","qty": 6}}' );

INSERT INTO orders (info) VALUES   
( '{ "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}}' );

3.查看json类型数据

select info from orders;

显示如下：

select info-> 'customer' AS customer from orders;

显示如下：

select info->> 'customer' AS customer from orders;

显示如下：

- The operator -> returns JSON object field by key.

- The operator ->> returns JSON object field by text.

- The operator -> returns a JSON object, you can chain it with the operator ->> to retrieve a specific node. For example, the following statement returns all products sold:

SELECT   info -> 'items' ->> 'product' as productFROM   ordersORDER BY product;

显示如下：

First info -> 'items' returns items as JSON objects. And then info->'items'->>'product' returns all products as text.

4.在where条件里面使用json

SELECT info ->> 'customer' AS customer,  info -> 'items' ->> 'product' AS product
  FROM orders
 WHERE CAST ( info -> 'items' ->> 'qty' AS INTEGER ) = 2；

显示如下：

5.在函数里面使用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 orders；

显示如下：

6.json类型的一些函数

json_each function
The json_each() function allows us to expand the outermost JSON object into a set of key-value pairs. See the following statement:

SELECT   json_each (info)FROM   orders;

显示如下：

json_object_keys function
To get a set of keys in the outermost JSON object, you use the json_object_keys() function. The following query returns all keys of the nested items object in the info column

SELECT json_object_keys (info->'items') FROM orders;

显示如下：

json_typeof function
The json_typeof() function returns type of the outermost JSON value as a string. It can be number, boolean, null, object, array, and string.
The following query return the data type of the items:

SELECT json_typeof (info->'items') FROM orders;

显示如下：

The following query returns the data type of the qty field of the nested items JSON object.

SELECT json_typeof ( info->'items'->'qty') FROM orders;

显示如下：