Postgresql JSON using WITH RECURSIVE

Question

My json is

[
  {
    "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
  }
]

I need to iterate the id (only when it is null and no id key) with a number and form back the same json as below even though the key (id) is missing . It has to be automatic id generation because I never know how many elements are present in this aggregation.

[
  {
    "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
  }
]

I believe RECURSIVE CTE work, but I'm unable to find a way to work this out. Please help

score 1 · Answer 1 · answered Mar 21 '20 at 10:21

You could unnest the array and replace any null value for id with the array index. However, this does not guarantee unique IDs as there could be an array index that is already used.

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)

Postgresql JSON using WITH RECURSIVE

1 Answers1