CODEWITHSUNDEEP
jsoncommon-table-expressionpostgresql-11postgresql-12
user1720827
user1720827

Reputation: 147

Postgresql JSON using WITH RECURSIVE

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

Upvotes: 0

Views: 117

Answers (1)

user330315
user330315

Reputation:

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)

Upvotes: 1

Related Questions