Conditional update with jsonb_set()

I have a table in a Postgres 11.3 database with a jsonb column.

Trying to update all objects inside a nested array name "iProps".

If the path {iProps -> value -> rules -> ao -> sc} is an object, then the path should be updated from an object to a string with the value {iProps -> value -> rules -> ao -> sc -> name}

If the path {iProps -> value -> rules -> ao -> sc} is not present, then the object should be left unchanged.

Test setup with query: Fiddle link

Desired result:

{
"iProps": [
{
"value": {
"rules": [
{
"ao": {
"set": "get"
},
"name": "PRule"
},
{
"ao": {
"sc": "name1"

}
},
{
"ao": {
"sc": "name2"

}
},
{
"ao": {
"sc": "name3"
}
}
]
}
}
]
}


I have modified the query and linked in the fiddle. Can someone take a look to see if it's right?

Continue reading...