Postgresql JSON using WITH RECURSIVE

Question

我的json是

[
{
"id": null,
"st": "value1",
"key": 1,
"value": "i am calling from",
"description": null
},
{
"st": "value2",
"key": 5,
"value": "i am calling from",
"description": null
},
{
"id": 25,
"st": "value3",
"key": 1,
"value": "i am calling from",
"description": null
}
]

我需要用一个数字迭代id(只有当它为null且没有id键时(，并形成与下面相同的json，即使缺少键(id(。它必须是自动生成id，因为我永远不知道这个聚合中有多少元素。

[
{
"id": 1,
"st": "value1",
"key": 1,
"value": "i am calling from",
"description": null
},
{
"id": 2,
"st": "value2",
"key": 5,
"value": "i am calling from",
"description": null
},
{
"id": 25,
"st": "value3",
"key": 1,
"value": "i am calling from",
"description": null
}
]

我相信递归CTE有效，但我找不到解决这个问题的方法。请帮助

Answer 1

您可以运行数组，并用数组索引替换id的任何空值。但是，这并不能保证ID是唯一的，因为可能存在已经使用的数组索引。

select jsonb_agg(
case 
when t.d ->> 'id' is null then t.d||jsonb_build_object('id', t.idx)
else t.d
end
)
from jsonb_array_elements('[
{
"id": null,
"st": "value1",
"key": 1,
"value": "i am calling from",
"description": null
},
{
"st": "value2",
"key": 5,
"value": "i am calling from",
"description": null
},
{
"id": 25,
"st": "value3",
"key": 1,
"value": "i am calling from",
"description": null
}
]'::jsonb) with ordinality as t(d,idx)