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;