PostgreSQL JSON

准备

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/