I’m having a hard time trying to understand how can I switch results within my query.
I have the following piece that runs into an Oracle database:
TO_NCLOB(JSON_OBJECT(
'name' VALUE TRIM(tb1.DS_NAME),
'birth' VALUE tb2.DT_BIRTH,
'id' VALUE TRIM(tb2.P_ID),
'email' VALUE tb3.DS_EMAIL,
'phone' VALUE tb3.NUM_PHONE,
'add' VALUE (SELECT JSON_ARRAYAGG(TRIM(tb4.ADD))
FROM SCHEMA1.TABLE_4 tb4
INNER JOIN SCHEMA1.TABLE_5 tb5
ON tb6.ID = tb5.ID
WHERE tb4.ID_F = tb5.ID_F),
'formula' VALUE (SELECT JSON_ARRAYAGG(TRIM(tb4.FORM))
FROM SCHEMA1.TABLE_4 tb4
INNER JOIN SCHEMA1.TABLE_5 tb5
ON tb6.ID = tb6.ID_U
WHERE tb4.ID_FORM = tb5.ID_FORM),
'code' VALUE (SELECT JSON_ARRAYAGG(TRIM(tb7.CODE))
FROM SCHEMA1.TABLE_4 tb4
INNER JOIN SCHEMA1.TABLE_5 tb5
ON tb6.ID = tb5.ID_U
INNER JOIN SCHEMA1.TABLE_8 tb8
ON tb5.ID_E = tb8.ID_E
INNER JOIN SCHEMA1.TABLE_7 tb7
ON tb8.ID_UN = tb7.ID_P))) AS USER_INFO
I need to transform the current output which is something like this:
{
"name": "JOSEPH STANLY",
"id": "5821451552112234",
"email": "[email protected]",
"phone": "3585445551451155",
"birth": "1980-01-29",
"add": [
"255521",
"357441"
],
"formula": [
"Architect",
"Interior Designer"
],
"code": [
"8955445",
"1225587"
]
}
Into something like this:
[
{
"name": "JOSEPH STANLY",
"id": "5821451552112234",
"email": "[email protected]",
"phone": "3585445551451155",
"birth": "1980-01-29",
"add": "223505",
"formula": "Architect",
"code": "8955445",
},
{
"name": "JOSEPH STANLY",
"id": "5821451552112234",
"email": "[email protected]",
"phone": "3585445551451155",
"birth": "1980-01-29",
"add": "1980-01-29",
"formula": "Interior Designer",
"code": "1225587",
}
]
As you can see, some of the fields that compose the field USER_INFO have multiple information. How can I split like the example above?
I tried removing the JSON_ARRAYAGG within the SELECT, but that didn’t work.
3
with data(js) as (
select q'~{
"name": "JOSEPH STANLY",
"id": "5821451552112234",
"email": "[email protected]",
"phone": "3585445551451155",
"birth": "1980-01-29",
"add": [
"255521",
"357441"
],
"formula": [
"Architect",
"Interior Designer"
],
"code": [
"8955445",
"1225587"
]
}~'
)
select
json_arrayagg(
json_object(
ta.name, ta.id, ta.email, ta.phone, ta.birth,
"add", formula, code
)
)
from
data d,
json_table(
js,
'$'
columns (
name path '$.name',
id path '$.id',
email path '$.email',
phone path '$.phone',
birth path '$.birth',
nested path '$.add[*]'
columns (
idx for ordinality,
"add" path '$'
)
)
) ta
left join
json_table(
js,
'$'
columns (
id path '$.id',
nested path '$.formula[*]'
columns (
idx for ordinality,
formula path '$'
)
)
) tf on ta.id = tf.id and ta.idx = tf.idx
left join json_table(
js,
'$'
columns (
id path '$.id',
nested path '$.code[*]'
columns (
idx for ordinality,
code path '$'
)
)
) tc on ta.id = tc.id and ta.idx = tc.idx
;
[
{"name":"JOSEPH STANLY","id":"5821451552112234","email":"[email protected]","phone":"3585445551451155","birth":"1980-01-29","add":"255521","formula":"Architect","code":"8955445"},
{"name":"JOSEPH STANLY","id":"5821451552112234","email":"[email protected]","phone":"3585445551451155","birth":"1980-01-29","add":"357441","formula":"Interior Designer","code":"1225587"}
]