I am working for the first time with .JSON data stored in a Oracle DB (BLOB) column, and an having issues with getting my SQL query to render data as desired. This is also my first time using JSON_TABLE function in SQL and making data-node path references; nested or direct.
Further below is a dumbed-down version of my SQL query, and a dumbed-down version of my .json record BLOB record(s) in question reduced from 56 entries with all columns to just 3 records with example columns included.
As can be seen, parent/child-nodes of concern are primarily:
All columns in my nested path '$.evaluations[*].evaluation_list[*].evaluation_meta' columns reference are returning as expected, and likewise for the direct path references for anything under the channel_meta node (ex.) path '$.evaluations[*].channel_meta.teamName[0]'.
However for data under the scores_obtained node, I get null if I attempt to reference via nested path '$.evaluations[*].evaluation_list[*].scores_obtained', and direct references like path '$.evaluations[*].evaluation_list[*].scores_obtained.final_score[0]' are returning incorrect values, almost like off-by-one reference?
My 6/18 record for instance should return a final_score of "80" and a percent_score of "94.12", but is instead returning "70" for the direct path attempt and "82.35" for the percent_score. And the correct values for the 6/18 record seem to be returning under the 5/24 record, and so forth down the line as if off-by-x reference somehow.
Can anyone spot where I may be going wrong here? I am struggling to figure out why I am successfully referencing only the 2 of my 3 child-node properties, all of which are containers and not array objects themselves. Thanks!
JSON:
{
"value": {
"page": 1,
"size": 56,
"total_pages": 1,
"total_size": 56,
"evaluation_forms": [
{
"template": {
},
"evaluations": [
{
"channel_meta": {
"teamName": [
"Team1"
],
"audioFileName": [
"509782716366"
],
"inQueueSeconds": [
"86.824"
]
},
"evaluation_list": [
{
"evaluation_meta": {
"id": "6671a10f965337086e7829e8",
"evaluator_name": "Evaluator1",
"agent_name": "Agent1",
"created_at": "2024-06-18T15:07:50.435Z",
"modified_at": "2024-06-18T15:07:50.435Z",
"status": "SUBMITTED"
},
"response": {
},
"scores_obtained": {
"final_score": 80.0,
"total_points": 85.0,
"percent_score": 94.12,
"grade_assigned": "Meets Expectations"
}
}
]
},
{
"channel_meta": {
"teamName": [
"Team1"
],
"audioFileName": [
"508581330938"
],
"inQueueSeconds": [
"4.404"
]
},
"evaluation_list": [
{
"evaluation_meta": {
"id": "6650b61ebdd8b70c0af26db4",
"evaluator_name": "Evaluator2",
"agent_name": "Agent2",
"created_at": "2024-05-24T15:54:41.468Z",
"modified_at": "2024-05-24T15:54:41.468Z",
"status": "SUBMITTED"
},
"response": {
},
"scores_obtained": {
"final_score": 70.0,
"total_points": 85.0,
"percent_score": 82.35,
"grade_assigned": "Needs Work",
}
}
]
},
{
"channel_meta": {
"teamName": [
"Team1"
],
"audioFileName": [
"508887908641"
],
"inQueueSeconds": [
"9.133"
]
},
"evaluation_list": [
{
"evaluation_meta": {
"id": "6658da061892a3009f5164b9",
"evaluator_name": "Evaluator2",
"agent_name": "Agent3",
"created_at": "2024-05-30T19:58:17.724Z",
"modified_at": "2024-05-30T19:58:17.724Z",
"status": "SUBMITTED"
},
"response": {
},
"scores_obtained": {
"final_score": 80.0,
"total_points": 85.0,
"percent_score": 94.12,
"grade_assigned": "Meets Expectations"
}
}
]
},
]
}
]
}
}
SQL:
select * from (
select
rank () over(partition by evaluation_id order by date_loaded desc) eval_rank,
b.evaluation_created_at,
b.evaluation_modified_at,
--debugging
b.final_score final_score_nested,
b.final_score_path final_score_path,
b.percent_score,
b.evaluation_id,
b.evaluator_name,
b.agent_name,
b.evaluation_status,
b.teamName,
b.audioFileName,
b.inQueueSeconds
from SCHEMAXYZ.TABLE_WITH_BLOB_COLUMN a
join json_table(a.evaluation_json, '$.value.evaluation_forms[*]'
ERROR ON ERROR
columns (
template_id path '$.template.id',
nested path '$.evaluations[*].evaluation_list[*].evaluation_meta' columns (
evaluation_id path '$.id',
evaluator_name,
evaluation_agent_id path '$.agent_id',
agent_name,
evaluation_created_at path '$.created_at',
evaluation_modified_at path '$.modified_at',
evaluation_status path '$.status'
),
----------------------------------------------------------------------------
--NOT WORKING (returns NULL)
nested path '$.evaluations[*].evaluation_list[*].scores_obtained' columns (
final_score
),
--NOT WORKING (returns next-level record somehow ex. 6/18 "70" instead of "80" for final score)
final_score_path path '$.evaluations[*].evaluation_list[*].scores_obtained.final_score[0]',
percent_score path '$.evaluations[*].evaluation_list[*].scores_obtained.percent_score[0]',
----------------------------------------------------------------------------
teamName path '$.evaluations[*].channel_meta.teamName[0]',
audioFileName path '$.evaluations[*].channel_meta.audioFileName[0]',
inQueueSeconds path '$.evaluations[*].channel_meta.inQueueSeconds[0]'
)
) b on 1=1
) mr
WHERE EVALUATION_ID IS NOT NULL
AND MR.EVALUATION_STATUS NOT IN ('DELETED','NA')
AND MR.EVAL_RANK = 1
AND evaluation_id IN (
'6671a10f965337086e7829e8', --6/18
'6650b61ebdd8b70c0af26db4', --5/24
'6658da061892a3009f5164b9' --5/30
)
ORDER BY evaluation_created_at DESC
You haven't given your expected output but you appear to want to use nested nested paths:
select *
from (
select rank () over(partition by evaluation_id order by date_loaded desc) eval_rank,
b.evaluation_created_at,
b.evaluation_modified_at,
--debugging
b.final_score,
b.percent_score,
b.evaluation_id,
b.evaluator_name,
b.agent_name,
b.evaluation_status,
b.teamName,
b.audioFileName,
b.inQueueSeconds
from SCHEMAXYZ.TABLE_WITH_BLOB_COLUMN a
CROSS JOIN JSON_TABLE(
a.evaluation_json,
'$.value.evaluation_forms[*]'
ERROR ON ERROR
COLUMNS (
template_id path '$.template.id',
NESTED PATH '$.evaluations[*]'
COLUMNS (
teamName path '$.channel_meta.teamName[0]',
audioFileName path '$.channel_meta.audioFileName[0]',
inQueueSeconds path '$.channel_meta.inQueueSeconds[0]',
NESTED PATH '$.evaluation_list[*]'
COLUMNS (
evaluation_id path '$.evaluation_meta.id',
evaluator_name path '$.evaluation_meta.evaluator_name',
evaluation_agent_id path '$.evaluation_meta.agent_id',
agent_name path '$.evaluation_meta.agent_name',
evaluation_created_at path '$.evaluation_meta.created_at',
evaluation_modified_at path '$.evaluation_meta.modified_at',
evaluation_status path '$.evaluation_meta.status',
final_score path '$.scores_obtained.final_score',
percent_score path '$.scores_obtained.percent_score'
)
)
)
) b
)
WHERE EVALUATION_ID IS NOT NULL
AND EVALUATION_STATUS NOT IN ('DELETED','NA')
AND EVAL_RANK = 1
AND evaluation_id IN (
'6671a10f965337086e7829e8', --6/18
'6650b61ebdd8b70c0af26db4', --5/24
'6658da061892a3009f5164b9' --5/30
)
ORDER BY evaluation_created_at DESC
Which, for the sample data:
CREATE TABLE TABLE_WITH_BLOB_COLUMN (
date_loaded DATE,
evaluation_json BLOB CHECK (evaluation_json IS JSON)
);
INSERT INTO TABLE_WITH_BLOB_COLUMN (date_loaded, evaluation_json)
SELECT SYSDATE, '{
"value": {
"page": 1,
"size": 56,
"total_pages": 1,
"total_size": 56,
"evaluation_forms": [
{
"template": {
},
"evaluations": [
{
"channel_meta": {
"teamName": [
"Team1"
],
"audioFileName": [
"509782716366"
],
"inQueueSeconds": [
"86.824"
]
},
"evaluation_list": [
{
"evaluation_meta": {
"id": "6671a10f965337086e7829e8",
"evaluator_name": "Evaluator1",
"agent_name": "Agent1",
"created_at": "2024-06-18T15:07:50.435Z",
"modified_at": "2024-06-18T15:07:50.435Z",
"status": "SUBMITTED"
},
"response": {
},
"scores_obtained": {
"final_score": 80.0,
"total_points": 85.0,
"percent_score": 94.12,
"grade_assigned": "Meets Expectations"
}
}
]
},
{
"channel_meta": {
"teamName": [
"Team1"
],
"audioFileName": [
"508581330938"
],
"inQueueSeconds": [
"4.404"
]
},
"evaluation_list": [
{
"evaluation_meta": {
"id": "6650b61ebdd8b70c0af26db4",
"evaluator_name": "Evaluator2",
"agent_name": "Agent2",
"created_at": "2024-05-24T15:54:41.468Z",
"modified_at": "2024-05-24T15:54:41.468Z",
"status": "SUBMITTED"
},
"response": {
},
"scores_obtained": {
"final_score": 70.0,
"total_points": 85.0,
"percent_score": 82.35,
"grade_assigned": "Needs Work",
}
}
]
},
{
"channel_meta": {
"teamName": [
"Team1"
],
"audioFileName": [
"508887908641"
],
"inQueueSeconds": [
"9.133"
]
},
"evaluation_list": [
{
"evaluation_meta": {
"id": "6658da061892a3009f5164b9",
"evaluator_name": "Evaluator2",
"agent_name": "Agent3",
"created_at": "2024-05-30T19:58:17.724Z",
"modified_at": "2024-05-30T19:58:17.724Z",
"status": "SUBMITTED"
},
"response": {
},
"scores_obtained": {
"final_score": 80.0,
"total_points": 85.0,
"percent_score": 94.12,
"grade_assigned": "Meets Expectations"
}
}
]
},
]
}
]
}
}' FROM DUAL;
Outputs:
| EVAL_RANK | EVALUATION_CREATED_AT | EVALUATION_MODIFIED_AT | FINAL_SCORE | PERCENT_SCORE | EVALUATION_ID | EVALUATOR_NAME | AGENT_NAME | EVALUATION_STATUS | TEAMNAME | AUDIOFILENAME | INQUEUESECONDS |
|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | 2024-06-18T15:07:50.435Z | 2024-06-18T15:07:50.435Z | 80 | 94.12 | 6671a10f965337086e7829e8 | Evaluator1 | Agent1 | SUBMITTED | Team1 | 509782716366 | 86.824 |
| 1 | 2024-05-30T19:58:17.724Z | 2024-05-30T19:58:17.724Z | 80 | 94.12 | 6658da061892a3009f5164b9 | Evaluator2 | Agent3 | SUBMITTED | Team1 | 508887908641 | 9.133 |
| 1 | 2024-05-24T15:54:41.468Z | 2024-05-24T15:54:41.468Z | 70 | 82.35 | 6650b61ebdd8b70c0af26db4 | Evaluator2 | Agent2 | SUBMITTED | Team1 | 508581330938 | 4.404 |