PostgreSQL与SQLite JSONPath语法差异及跨库适配咨询

问题背景 #

我正在开发一个Java服务项目，该服务与存储JSON对象的数据库交互。JSON对象名为scanResultCollection，包含顶层属性organization。项目在本地单机模式下使用SQLite，部署后自动切换为PostgreSQL。

相关代码 #

EntityManager entityManager = ctx.attribute(App.RESULT_TABLE_ENTITY_MANAGER);
var criteriaBuilder = entityManager.getCriteriaBuilder();
CriteriaQuery<Result> criteriaQuery = criteriaBuilder.createQuery(Result.class);
Root<Result> root = criteriaQuery.from(Result.class);

var organizationPath = JPathHelpers.getJsonPathFor(
        List.of(ScanResultCollection.ORGANIZATION_KEY)
);
LOG.info("organizationPath is: {}", organizationPath);
var predicate = criteriaBuilder.equal(
        criteriaBuilder.function(
                "jsonb_extract_path_text",
                //"json_extract", // @TODO this should be a config value as it's db specific
                String.class,
                // TODO see note in Result entity for why we're not using the generated metamodel for now
                // TODO when looking into the ScanResultCollection json object
                root.get(SCAN_RESULT_COLLECTION_ATTRIBUTE),
                //criteriaBuilder.literal(organizationPath)
                criteriaBuilder.literal("organization")
        ),
        org
);

criteriaQuery.select(root).where(predicate);
var query = entityManager.createQuery(criteriaQuery);
List<Result> results = query.getResultList();

核心需求 #

根据调用方提供的organization值，查询所有匹配该值的scanResultCollection对象。

遇到的问题 #

在SQLite中使用json_extract函数，organizationPath解析为$.organization可正常工作；切换到PostgreSQL时，需将函数改为jsonb_extract_path_text，但$.organization无法返回结果，必须使用纯字符串"organization"才能正常查询。

查阅文档发现，PostgreSQL的JSONPath定义与其他场景（如SQLite、IETF草案、Jayway JSONPath）存在差异，尤其是$的含义。

具体疑问 #

1. 为何"$.organization"无法同时在SQLite和PostgreSQL中生效？PostgreSQL（及Oracle、SqlServer）将$定义为“被查询JSON值的上下文项”，这不应该等同于根JSON元素吗？
2. 是否可通过Hibernate或其他Java库统一JSONPath语法，实现跨多数据库适配？目前观察到PostgreSQL、Oracle、SqlServer语法一致，但与SQLite不同，希望了解实现方法。

同时困惑于主流数据库厂商为何会将JSONPath实现为与通用规范不一致的形式。

解答 #

问题1：为何$.organization无法跨库生效？ #

这是因为不同数据库对JSON查询函数的参数设计逻辑完全不同：

• SQLite的json_extract：接受完整JSONPath表达式（如$.organization），它依赖标准JSONPath语法来定位节点，必须明确从根节点$开始声明路径。
• PostgreSQL的jsonb_extract_path_text（含Oracle、SQL Server同类函数）：本质是层级键名提取函数，参数直接传入键名即可，不需要JSONPath的$前缀。这类函数的设计思路是将JSON视为嵌套键值结构，传入的参数是层级键的序列，而非完整的JSONPath表达式。

你提到的PostgreSQL中$作为“上下文项”，是针对它的原生jsonpath查询体系（比如jsonb_path_query函数或@>运算符），而jsonb_extract_path_text属于早期的键提取API，不遵循完整JSONPath规范，两者是完全独立的查询体系。

问题2：如何实现跨数据库JSON查询适配？ #

可以通过Hibernate的原生支持或自定义适配层实现统一语法，推荐两种方案：

方案1：使用Hibernate ORM 6+的原生JSON映射

Hibernate 6引入了对JSON类型的原生支持，通过注解映射JSON字段后，可使用JPA Criteria API的统一语法查询，Hibernate会自动适配底层数据库：

1. 在Result实体中为scanResultCollection字段添加JSON映射注解：

   @Column(columnDefinition = "jsonb")
   @JdbcTypeCode(SqlTypes.JSON)
   private ScanResultCollection scanResultCollection;
   

2. 使用统一的Criteria API查询：

   var predicate = criteriaBuilder.equal(
       root.get("scanResultCollection").get("organization"),
       org
   );
   

   Hibernate会自动转换为对应数据库的SQL：
   • SQLite：生成json_extract(scan_result_collection, '$.organization') = ?
   • PostgreSQL：生成jsonb_extract_path_text(scan_result_collection, 'organization') = ?

方案2：自定义数据库适配函数（低版本Hibernate）

如果使用Hibernate 5及以下版本，可以自定义通用JSON提取函数，通过扩展Dialect实现适配：

1. 定义自定义函数json_extract_value，在SQLite方言中映射为json_extract(?, ?)，参数传入$.organization；
2. 在PostgreSQL方言中映射为jsonb_extract_path_text(?, ?)，参数传入organization；
3. 代码中直接调用该自定义函数，Hibernate会根据当前数据库自动切换实现：

   var predicate = criteriaBuilder.equal(
       criteriaBuilder.function(
           "json_extract_value",
           String.class,
           root.get(SCAN_RESULT_COLLECTION_ATTRIBUTE),
           criteriaBuilder.literal(organizationPath)
       ),
       org
   );
   

关于厂商实现不一致的原因 #

JSON查询的标准化进程滞后于数据库厂商的落地实现：

• 早期各厂商在添加JSON支持时，没有统一的行业规范可遵循，优先根据自身数据库的架构、性能需求设计API；
• 后来IETF推出JSONPath草案时，多数厂商已经固化了原有实现，为了兼容存量代码无法彻底切换到新规范；
• 不同数据库对JSON的存储优化方向不同（如PostgreSQL的jsonb是二进制优化存储，SQLite是文本存储），也导致查询API的设计逻辑差异。

内容的提问来源于stack exchange，提问作者snerd