查询简化的JSONB形式JSONB列包含嵌套JSON从Postgresql数据库?

Question

我的数据库中有一个包含嵌套JSON的JSONB列。我需要查询这个列，但我想保留JSON结构，只删除不必要的键值。假设这是数据库中的样例记录:

{
"id": 1,
"foo": "bar",
"something": "anything",
"something_else": "anything_else",
"video": [
{
"id": 1,
"type": "slow",
"width": 700,
"height": 900
},
{
"id": 2,
"type": "medium",
"width": 900,
"height": 100
},
{
"id": 3,
"type": "fast",
"width": 700,
"height": 900
}
],
"image": {
"candidates": [
{
"width": 480,
"height": 600,
"scans_profile": "e35"
},
{
"width": 480,
"height": 600,
"scans_profile": "e35"
}
]
}
}

我需要得到:

{
"id": 1,
"foo": "bar",
"video": [
{
"id": 1,
"width": 700,
"height": 900
},
{
"id": 2,
"width": 900,
"height": 100
},
{
"id": 3,
"width": 700,
"height": 900
}
],
"image": {
"candidates": [
{
"scans_profile": "e35"
},
{
"scans_profile": "e35"
}
]
}
}

是否有一种优雅、简单的方法来做到这一点?或者还有别的办法吗?

我正在使用postgres 13.3

Answer 1

如果源数据总是具有相同的格式，则可以删除不需要的路径。这是可行的，因为你过滤掉了特定的键&不做其他变换。如果在编写此查询时，源可能具有可变数量的键或可变数组长度，则此操作将失败，因为您不会删除其他未知路径。

。如果顶级键总是[id, key, something, something_else, video, image]

其中video总是一个json对象数组，键值为[id, type, width, height]，长度为3

和image是一个具有一个键candidates的对象，它是一个长度为2的键[width, height, scans_profile]的对象数组，然后您可以编写以下内容:

SELECT
my_json_column 
#- '{something}'
#- '{something_else}'
#- '{video,0,type}'
#- '{video,1,type}'
#- '{video,2,type}'
#- '{image,candidates,0,width}'
#- '{image,candidates,1,width}'
#- '{image,candidates,0,height}'
#- '{image,candidates,1,height}'
AS my_new_json_column
FROM my_table

这里有一个小提琴:https://dbfiddle.uk/?rdbms=postgres_13&fiddle=508e6b864ff811289b4610c4c2380df8

否则，您唯一的选择是构建一个新的jsonb对象

SELECT
jsonb_build_object(
'id', my_json_column->'id',
'foo', my_json_column->'foo',
'video', video.video,
'image', jsonb_build_object('candidates', image_candidates.image_candidates)
) AS my_new_json_column
FROM my_table
LEFT JOIN LATERAL (
SELECT jsonb_agg(jsonb_build_object(
'id', y->'id', 
'width', y->'width', 
'height', y->'height'
)) video
FROM jsonb_path_query(my_json_column, '$.video[*]') v(y)
) video ON true
LEFT JOIN LATERAL (
SELECT jsonb_agg(jsonb_build_object(
'scans_profile', y->'scans_profile'
)) image_candidates
FROM jsonb_path_query(my_json_column, '$.image.candidates[*]') v(y)
) image_candidates ON true