Postgresql JSON使用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)