Team,
was playing with Oracle 23c feature of json_transform - here is my sample data.
CREATE TABLE REPOSITORY_SETTINGS (
REPOSITORY_ID VARCHAR2(256) NOT NULL PRIMARY KEY,
REPOSITORY_SETTINGS json );
insert into REPOSITORY_SETTINGS values(
'ocid1.devopsrepository.dev.dev.amaaaaaavuj6teyapwt3576zequ4nuk5cffr3kyggxxe3nc3uipnagaw27ka',
'{
"mergeChecks": {
"lastBuildSucceeded": "ENABLED"
},
"mergeSettings": {
"defaultMergeStrategy": "FAST_FORWARD",
"allowedMergeStrategies": [
"FAST_FORWARD",
"SQUASH"
]
},
"approvalRules": {
"items": [
{
"name": "rule1",
"destinationBranch": "main",
"reviewerIds": ["SYS", "SYSTEM"],
"minApprovalsCount": 1
},
{
"name": "rule2",
"destinationBranch": "master",
"reviewerIds": ["SYS"],
"minApprovalsCount": 1
}
]
}}');
CREATE TABLE PRINCIPAL (
PRINCIPAL_ID VARCHAR2(256) NOT NULL PRIMARY KEY,
PRINCIPAL_NAME VARCHAR2(256),
PRINCIPAL_TYPE VARCHAR2(256) NOT NULL CHECK ("PRINCIPAL_TYPE" IN ('USER', 'SERVICE')),
EMAIL VARCHAR2(256),
DOMAIN_ID VARCHAR2(256),
TIME_UPDATED TIMESTAMP(6)
);
insert into PRINCIPAL values(
'SYS','Sys_name','USER',
'fake@gamil.com','domain',null);
insert into PRINCIPAL values(
'SYSTEM','Sys_name','USER',
'fake@gamil.com','domain',null);
and the requirement is " we are trying to get back the original record in “REPOSITORY_SETTINGS” table like this by join reviewerId with the principalId in Principal Table"
and the final output should be like this
'ocid1.devopsrepository.dev.dev.amaaaaaavuj6teyapwt3576zequ4nuk5cffr3kyggxxe3nc3uipnagaw27ka',
'{
"mergeChecks": {
"lastBuildSucceeded": "ENABLED"
},
"mergeSettings": {
"defaultMergeStrategy": "FAST_FORWARD",
"allowedMergeStrategies": [
"FAST_FORWARD",
"SQUASH"
]
},
"approvalRules": {
"items": [
{
"name": "rule1",
"destinationBranch": "main",
"reviewers": [
{"userId":"SYS",
"userName":"Sys_name",
"email":"fake@gamil.com"} ,
{ "userId":"SYSTEM",
"userName":"Sys_name",
"email":"fake@gamil.com"}
],
"minApprovalsCount": 1
},
{
"name": "rule2",
"destinationBranch": "master",
"reviewers": [
{
"userId":"SYS",
"userName":"Sys_name",
"email":"fake@gamil.com"}],
"minApprovalsCount": 1
}
]
}}'
utilizing the json_transform "nested path" feature & works fine with hard coding the values
demo@FREEPDB1> select json_transform( REPOSITORY_SETTINGS, nested path '$.approvalRules.items[*]'
2 ( set '@.reviewerIds[*]' = (select json_object( 'userId' value p.PRINCIPAL_ID , 'userName' value p.PRINCIPAL_NAME , 'email' value p.email
3 from PRINCIPAL p where p.PRINCIPAL_ID = 'SYS' ) ) )
4 from REPOSITORY_SETTINGS ;
JSON_TRANSFORM(REPOSITORY_SETTINGS,NESTEDPATH'$.APPROVALRULES.ITEMS[*]'(SET'@.RE
--------------------------------------------------------------------------------
{
"mergeChecks" :
{
"lastBuildSucceeded" : "ENABLED"
},
"mergeSettings" :
{
"defaultMergeStrategy" : "FAST_FORWARD",
"allowedMergeStrategies" :
[
"FAST_FORWARD",
"SQUASH"
]
},
"approvalRules" :
{
"items" :
[
{
"name" : "rule1",
"destinationBranch" : "main",
"reviewerIds" :
[
{
"userId" : "SYS",
"userName" : "Sys_name",
"email" : "fake@gamil.com"
},
{
"userId" : "SYS",
"userName" : "Sys_name",
"email" : "fake@gamil.com"
}
],
"minApprovalsCount" : 1
},
{
"name" : "rule2",
"destinationBranch" : "master",
"reviewerIds" :
[
{
"userId" : "SYS",
"userName" : "Sys_name",
"email" : "fake@gamil.com"
}
],
"minApprovalsCount" : 1
}
]
}
}
demo@FREEPDB1>
but not working with dynamic handling of "$.approvalRules.items[*].reviewerIds[*]" in the RHS of json_transform function call - any possible solution to this using json_transform(..) ?
demo@FREEPDB1> select json_transform( REPOSITORY_SETTINGS, nested path '$.approvalRules.items[*]'
2 ( set '@.reviewerIds[*]' = (select json_object( 'userId' value p.PRINCIPAL_ID , 'userName' value p.PRINCIPAL_NAME , 'email' value p.email )
3 from PRINCIPAL p where p.PRINCIPAL_ID = '$.approvalRules.items[*].reviewerIds[*]' ) ) )
4 from REPOSITORY_SETTINGS ;
JSON_TRANSFORM(REPOSITORY_SETTINGS,NESTEDPATH'$.APPROVALRULES.ITEMS[*]'(SET'@.RE
--------------------------------------------------------------------------------
{
"mergeChecks" :
{
"lastBuildSucceeded" : "ENABLED"
},
"mergeSettings" :
{
"defaultMergeStrategy" : "FAST_FORWARD",
"allowedMergeStrategies" :
[
"FAST_FORWARD",
"SQUASH"
]
},
"approvalRules" :
{
"items" :
[
{
"name" : "rule1",
"destinationBranch" : "main",
"reviewerIds" :
[
null,
null
],
"minApprovalsCount" : 1
},
{
"name" : "rule2",
"destinationBranch" : "master",
"reviewerIds" :
[
null
],
"minApprovalsCount" : 1
}
]
}
}