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;
显示如下:
![](https://img.laitimes.com/img/_0nNw4CM6IyYiwiM6ICdiwiInVGcq5CMkRjYiNTO0QTMmhDN4UmYxADN0YGZ0EzN5QmMilzNm9CX5d2bs92Yl1iclB3bsVmdlR2LcNWaw9CXt92Yu4GZjlGbh5yYjV3Lc9CX6MHc0RHaiojIsJye.jpeg)
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;