准备
CREATE TABLE orders (
id serial NOT NULL PRIMARY KEY,
info json NOT NULL
);
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}}');
查询
The operator -> returns JSON object field by key.
The operator ->> returns JSON object field by text.
SELECT info -> 'customer' AS customer FROM orders;
SELECT info -> 'items' ->> 'product' as product
FROM orders
ORDER BY product;
Ref
https://www.postgresqltutorial.com/postgresql-tutorial/postgresql-json/