PostgreSQL JSON 学习

创建示例表：

DROP TABLE IF EXISTS book_info_t;
CREATE TABLE book_info_t(
	id serial PRIMARY KEY,
	book_name VARCHAR(128),
	book_price NUMERIC(5,2),
	book_chapters JSONB,
	create_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);
-- 表描述信息
COMMENT ON TABLE book_info_t IS '书籍信息表';
-- 列描述信息
COMMENT ON COLUMN book_info_t.id IS '主键ID，自增';
COMMENT ON COLUMN book_info_t.book_name IS '书籍名称';
COMMENT ON COLUMN book_info_t.book_price IS '书籍价格';
COMMENT ON COLUMN book_info_t.book_chapters IS '书籍目录';
COMMENT ON COLUMN book_info_t.create_time IS '创建时间';

初始化数据：

INSERT INTO book_info_t(
	book_name,
	book_price,
	book_chapters
)
VALUES(
	'PostgreSQL实战',
	89.00,
	'[{"chapter_number":1,"chapter_title":"安装与配置接触"},{"chapter_number":2,"chapter_title":"客户端工具"},{"chapter_number":3,"chapter_title":"数据类型"}]'::JSONB
);

INSERT INTO book_info_t(
	book_name,
	book_price,
	book_chapters
)
VALUES(
	'Elasticsearch权威指南',
	79.80,
	'[{"chapter_number":1,"chapter_title":"快速入门"},{"chapter_number":2,"chapter_title":"安装部署"},{"chapter_number":3,"chapter_title":"API规范"},{"chapter_number":4,"chapter_title":"操作文档"}]'::JSONB
);

INSERT INTO book_info_t(
	book_name,
	book_price,
	book_chapters
)
VALUES(
	'愿你的青春不负梦想',
	36.00,
	'[{"chapter_number":1,"chapter_title":"青春"},{"chapter_number":2,"chapter_title":"梦想"},{"chapter_number":3,"chapter_title":"成长"},{"chapter_number":4,"chapter_title":"奋斗"},{"chapter_number":5,"chapter_title":"事业"},{"chapter_number":6,"chapter_title":"生活"}]'::JSONB
);

通过jsonb_array_elements函数进行转换，WITH ORDINALITY用来返回记录的每一行行号

SELECT
	t1.id,
	t1.book_name AS 书名,
	t1.book_price AS 价格,
	t2.item,
	CAST(t2.item ->> 'chapter_number' AS INT) AS 章节编号,
	t2.item ->> 'chapter_title' AS 章节标题,
	t2.serial_number 
FROM
	book_info_t t1,
	jsonb_array_elements ( book_chapters ) WITH ORDINALITY t2 ( item, serial_number ) 

扩展学习资料：http://www.postgres.cn/docs/12/functions-json.html