PostgreSQL 如何按指定多键查询JSONB字段值并返回合并JSON对象

我在数据库中存储了JSONB类型的数据，需要提取传入的动态键数组对应的所有键值对，键名是可变的。

示例JSONB数据 #

{"key": "test2", "key1": "value", "key2": "value1", "key3": "value2", "key4": "value5", "name": "test"}

原有查询代码 #

@Query(value = "SELECT d.key, d.value " +
        " FROM Table table" +
        " JOIN jsonb_each_text(table.data) d ON true " +
        " WHERE table.type = ?1 AND table.time BETWEEN ?2 AND ?3"+
        " AND d.key IN ?4" , nativeQuery = true)
List<Object>findByData(String type, Long from, Long to, String[] keys);

原有返回结果 #

[
    [
        "key",
        "test2"
    ],
    [
        "key1",
        "value"
    ],
    [
        "name",
        "test"
    ]
]

期望返回结果 #

[{
    "key":"test2",
    "key1":"value",
    "name":"test"
}]

你只需要修改查询语句，使用PostgreSQL内置的json_object_agg聚合函数，把拆分出来的键值对重新聚合为单个JSON对象即可。如果需要返回数组包裹的结构，外层再套一次json_build_array就行。

修改后的查询代码 #

@Query(value = "SELECT json_build_array(json_object_agg(d.key, d.value)) " +
        " FROM Table table" +
        " JOIN jsonb_each_text(table.data) d ON true " +
        " WHERE table.type = ?1 AND table.time BETWEEN ?2 AND ?3" +
        " AND d.key IN ?4" , nativeQuery = true)
List<JsonNode> findByData(String type, Long from, Long to, String[] keys);

补充说明 #

• json_object_agg(d.key, d.value)会自动把所有符合条件的键值对聚合为一个JSON对象，正好匹配你需要的键值结构
• 外层的json_build_array是为了把单个JSON对象包裹在数组内，和你期望的返回格式完全对齐
• 返回类型可以调整为JsonNode（Jackson包下）或者String，避免手动解析数组的麻烦
• 如果你是按行查询、每行返回一个对应JSON对象，只需要在查询末尾加上GROUP BY table.id（对应你的表主键字段）即可

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