文档教程知识库【免费下载链接】til:memo: Today I Learned项目地址https://gitcode.com/gh_mirrors/ti/til点击查看免费下载在 PostgreSQL 的jsonb列中每行记录可以是对象、数组、字符串、数字、布尔值或 null 中的任意一种。本文讲解如何用json_typeof/jsonb_typeof函数精确判定每条记录的顶层 JSON 类型并介绍pg_typeof在此场景下的局限以及如何将类型判定用于过滤、分组统计等实战查询。场景一个 jsonb 列里塞了各种东西PostgreSQL 的 JSONB 数据类型 所指向的主题允许你在同一列的不同行中存放结构完全不同的 JSON 值。这是 JSON 列区别于关系型列的一大特性同一个列里第一行是对象、第二行是数组、第三行可能只是一个数字。当你需要对这一列做审计audit——比如弄清楚这列里到底存了哪些形态的数据、有多少行是对象、多少行是数组时第一个想到的往往是pg_typeof。但这条路走不通。误区pg_typeof 只告诉你它是 jsonbpg_typeof()是 PostgreSQL 通用的类型检查函数用于返回任意表达式的 SQL 数据类型。仓库中的另一篇笔记 checking-the-type-of-a-value.md 展示了它的典型用法 select pg_typeof(1); pg_typeof ----------- integer (1 row) select pg_typeof(true); pg_typeof ----------- boolean (1 row)但如果你对 jsonb 列使用它 select pg_typeof(my_jsonb_column) from my_table;它会一遍又一遍地输出jsonb——因为pg_typeof关心的是数据库层的类型列的类型是 jsonb而不是JSON 值内部的形态。正如原文档所说That is just gonna spit outjsonbover and over, like, I already know that.它只会反复输出 jsonb这个我本来就知道。pg_typeof无法区分 JSON 值内部是对象还是数组因为它根本不解析 JSON 内容。它只能回答这个列/表达式的 SQL 类型是什么而不是这条 JSON 记录的顶层结构是什么。正解jsonb_typeof 与 json_typeofPostgreSQL 专门提供了两个 JSON 处理函数来解决这个问题jsonb_typeof(jsonb)—— 针对jsonb类型json_typeof(json)—— 针对json类型两者返回的是字符串表示传入 JSON 值的顶层类型。可能的返回值共有六种返回值含义objectJSON 对象键值对集合arrayJSON 数组stringJSON 字符串numberJSON 数字booleanJSON 布尔值true / falsenullJSON null注意与 SQL NULL 的区别基础用法 select jsonb_typeof(my_jsonb_column) from my_table; jsonb_typeof -------------- object array string number boolean null ...原文档给出的这个示例正是审计场景的核心逐行列出每条记录的顶层类型一眼就能看出这列里混着哪几种形态。需要特别说明的是json_typeof和jsonb_typeof的差别仅在于入参类型。如果列的类型是jsonb就必须用jsonb_typeof如果列的类型是json则用json_typeof。两者返回的字符串结果集合完全一致。如果你在json列上误用了jsonb_typeof或反之PostgreSQL 会尝试隐式类型转换——通常json与jsonb之间可以互转但最稳妥的做法是让函数与列类型匹配。实战一过滤某种类型的记录jsonb_typeof的返回值是普通文本因此可以直接放进WHERE子句-- 找出所有顶层为数组的 JSONB 记录 select * from my_table where jsonb_typeof(my_jsonb_column) array; -- 找出所有顶层为对象的 JSONB 记录 select * from my_table where jsonb_typeof(my_jsonb_column) object;这在数据清洗时非常有用比如某列本来约定只存对象但历史数据里混进了数组和字符串用上面的查询即可精准定位不守规矩的行。实战二按类型分组统计把jsonb_typeof与GROUP BY结合就能一次性得到这列的类型分布这是审计的核心诉求。仓库中 count-records-by-type.md 演示了通用的按类型计数模式select type, count(*) from pokemon group by type套用到 JSONB 列上即为select jsonb_typeof(my_jsonb_column) as json_type, count(*) from my_table group by json_type order by json_type;输出示例json_type | count ------------------ array | 12 boolean | 3 null | 5 number | 8 object | 120 string | 4这样一个查询就能让你完整掌握列的构成判断是否需要补数据约束或拆分表结构。实战三判断嵌套字段的类型jsonb_typeof接受任意返回 jsonb 的表达式因此可以用-运算符取出嵌套值后再判定其类型。比如仓库文档 extracting-nested-json-data.md 提到的json_extract_path/-路径提取就可以与类型判定组合-- 判定每行记录中某个字段的顶层类型 select jsonb_typeof(my_jsonb_column - some_key) from my_table; -- 结合 WHERE 使用找出某个嵌套字段是数组的记录 select * from my_table where jsonb_typeof(my_jsonb_column - items) array;注意如果some_key在记录中不存在-会返回 SQL NULL此时jsonb_typeof(NULL)的结果也是 NULL 而不是字符串null。区分这两个空很重要JSON 中显式存在的null值 →jsonb_typeof返回字符串nullJSON 中根本不存在的键或整条记录本身就是 SQL NULL →jsonb_typeof返回 SQL NULL这也是为什么在分组统计时null类型和真正的无值需要分别对待。与 jsonb 相关的配套操作围绕 JSONB 列的日常操作仓库中还有几篇可以配合使用的笔记pretty-printing-jsonb-rows.mdjsonb_pretty()把单行输出的 jsonb 展开成易读的多行格式审计时配合jsonb_typeof使用效果更佳checking-the-type-of-a-value.mdpg_typeof()的完整介绍包括它对任意字符串无法区分text/varchar的局限与本文的pg_typeof 不适合判定 JSON 内部类型是同一类问题extracting-nested-json-data.md用-/json_extract_path提取嵌套 JSON 值是jsonb_typeof判断嵌套类型的搭档。小结判定 JSONB 列中记录的顶层类型正确工具是jsonb_typeofjsonb 列和json_typeofjson 列而不是通用的pg_typeof。前者返回object/array/string/number/boolean/null六种字符串之一可用于WHERE过滤、GROUP BY统计以及配合-运算符对嵌套字段做类型判定。理解JSON null 与 SQL NULL 的区别是避免审计结果失真的关键细节。赞分享文档教程知识库【免费下载链接】til:memo: Today I Learned项目地址https://gitcode.com/gh_mirrors/ti/til点击查看免费下载相关推荐Phoenix项目中PostgreSQL的JSON与JSONB类型深度解析Phoenix项目中PostgreSQL的JSON与JSONB类型深度解析 前言 在现代数据库应用中半结构化数据存储已成为刚需。PostgreSQL作为功能强可观测性AI 评测LLMOpsAI 应用人工智能Hibernate ORM JSON数据类型映射PostgreSQL与MySQL JSONB教程Hibernate ORM JSON数据类型映射PostgreSQL与MySQL JSONB教程 你是否还在为JSON数据在关系型数据库中的存储和查询烦恼本后端数据库ORMsqlx 实战PostgreSQL 中 Json/JsonB 类型的安全读写与编译期检查sqlx 实战PostgreSQL 中 Json/JsonB 类型的安全读写与编译期检查 本指南基于 sqlx 仓库中的 PostgreSQL JSON 示例数据库后端创作声明：本文部分内容由AI辅助生成（AIGC），仅供参考
