如何使用Practical SQL 2nd Edition处理JSON数据PostgreSQL中的JSON操作全解析【免费下载链接】practical-sql-2Code and Data for the Second Edition of Practical SQL by Anthony DeBarros, published by No Starch Press.项目地址: https://gitcode.com/gh_mirrors/pr/practical-sql-2Practical SQL 2nd Edition是一本由Anthony DeBarros撰写的实用SQL指南书中详细介绍了如何利用PostgreSQL数据库处理各种数据类型包括强大的JSON数据操作功能。PostgreSQL提供了对JSON数据的原生支持通过json和jsonb两种数据类型以及丰富的操作符和函数让用户能够高效地存储、查询和分析JSON格式数据。PostgreSQL中JSON与JSONB的核心区别PostgreSQL提供了两种JSON数据类型json和jsonb。在Chapter_16/Chapter_16.sql中作者推荐使用jsonb类型因为它支持索引且查询性能更优。json以原始文本形式存储保留所有空格和顺序但不支持索引jsonb以二进制格式存储自动优化结构并删除冗余空格支持GIN索引创建JSONB表的示例代码CREATE TABLE films ( id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY, film jsonb NOT NULL ); -- 添加GIN索引提升查询性能 CREATE INDEX idx_film ON films USING GIN (film);必备JSON数据提取操作符详解PostgreSQL提供了多种操作符用于从JSON数据中提取信息以下是最常用的几种1. 字段提取操作符-和--返回JSON格式的字段值-返回文本格式的字段值-- 返回JSON格式的标题 SELECT id, film - title AS title_json FROM films; -- 返回文本格式的标题 SELECT id, film - title AS title_text FROM films;2. 路径提取操作符#和#用于提取嵌套JSON结构中的值使用花括号指定路径-- 提取电影的MPAA评级JSON格式 SELECT id, film # {rating, MPAA} AS mpaa_rating FROM films; -- 提取第一个角色的名字文本格式 SELECT id, film # {characters, 0, name} AS first_character FROM films;高效JSONB查询 containment与existence操作符JSONB最强大的特性之一是支持containment包含和existence存在查询这使得复杂的JSON数据检索变得简单高效。1. Containment操作符和判断左侧JSON是否包含右侧JSON判断右侧JSON是否包含左侧JSON-- 查找标题为The Incredibles的电影 SELECT film - title AS title, film - year AS year FROM films WHERE film {title: The Incredibles}::jsonb;2. Existence操作符?、?|和??检查JSON是否包含指定的键或数组元素?|检查JSON是否包含数组中的任何一个元素?检查JSON是否包含数组中的所有元素-- 检查电影是否有rating键 SELECT film - title AS title FROM films WHERE film ? rating; -- 检查电影是否同时有rating和genre键 SELECT film - title AS title FROM films WHERE film ? {rating, genre};实用JSONB函数从查询到数据生成PostgreSQL提供了丰富的JSONB函数用于处理和转换JSON数据。1. 数组处理函数jsonb_array_length获取JSON数组的长度jsonb_array_elements将JSON数组展开为行-- 获取每部电影的角色数量 SELECT id, film - title AS title, jsonb_array_length(film - characters) AS num_characters FROM films; -- 将电影类型数组展开为行 SELECT id, film - title AS title, jsonb_array_elements_text(film - genre) AS genre FROM films;2. JSON生成函数to_json将SQL值转换为JSONjsonb_build_object构建JSON对象-- 将查询结果转换为JSON SELECT to_json(employees) AS json_rows FROM employees; -- 构建JSON对象并更新数据 UPDATE films SET film film || jsonb_build_object(studio, Pixar) WHERE film {title: The Incredibles}::jsonb;3. JSON修改函数jsonb_set更新JSON中指定路径的值-和#-删除JSON中的键或路径-- 更新电影类型数组 UPDATE films SET film jsonb_set(film, {genre}, film # {genre} || [World War II], true) WHERE film {title: Cinema Paradiso}::jsonb; -- 删除指定键 UPDATE films SET film film - studio WHERE film {title: The Incredibles}::jsonb;实战案例分析地震JSON数据在Chapter_16/Chapter_16.sql中作者展示了如何使用JSONB处理美国地质调查局的地震数据。以下是一些实用查询示例1. 查找最大地震SELECT earthquake # {properties, place} AS place, to_timestamp((earthquake # {properties, time})::bigint / 1000) AT TIME ZONE UTC AS time, (earthquake # {properties, mag})::numeric AS magnitude FROM earthquakes ORDER BY magnitude DESC NULLS LAST LIMIT 5;2. 提取地理坐标并转换为PostGIS点-- 添加地理信息列 ALTER TABLE earthquakes ADD COLUMN earthquake_point geography(POINT, 4326); -- 更新地理信息 UPDATE earthquakes SET earthquake_point ST_SetSRID( ST_MakePoint( (earthquake # {geometry, coordinates, 0})::numeric, (earthquake # {geometry, coordinates, 1})::numeric ), 4326)::geography;3. 查找特定区域的地震-- 查找距离美国俄克拉荷马州塔尔萨市中心50英里内的地震 SELECT earthquake # {properties, place} AS place, to_timestamp((earthquake - properties - time)::bigint / 1000) AT TIME ZONE UTC AS time, (earthquake # {properties, mag})::numeric AS magnitude FROM earthquakes WHERE ST_DWithin(earthquake_point, ST_GeogFromText(POINT(-95.989505 36.155007)), 80468) -- 50英里 80468米 ORDER BY time;学习资源与进一步探索要深入学习PostgreSQL的JSON功能推荐参考以下资源官方文档PostgreSQL官方文档提供了完整的JSON函数和操作符参考Practical SQL 2nd Edition书中Chapter_16提供了更多实际案例和详细解释练习文件项目中的Chapter_16/earthquakes.json和Chapter_16/films.json可用于实践通过这些工具和技术你可以充分利用PostgreSQL的JSON功能来处理半结构化数据为数据分析和应用开发提供强大支持。无论是处理API响应、日志数据还是复杂的嵌套结构PostgreSQL的JSONB都能提供高效且灵活的解决方案。要开始使用这些功能你可以克隆项目仓库git clone https://gitcode.com/gh_mirrors/pr/practical-sql-2然后查看Chapter_16目录中的示例代码和数据文件。【免费下载链接】practical-sql-2Code and Data for the Second Edition of Practical SQL by Anthony DeBarros, published by No Starch Press.项目地址: https://gitcode.com/gh_mirrors/pr/practical-sql-2创作声明：本文部分内容由AI辅助生成（AIGC），仅供参考
