How to parse or work with a JSON POST request to Oracle relational data table-CodePudding

I am building a .net core web api using Dapper and Oracle 19c. The application will receive a POST request similar to the one below, and needs to return a value (Salary) from the same table. It needs to loop over the JSON and return the salary filtering on name, id, and year that match relational data in an Employees table which also contains the salary and other information for each employee. I am new to Oracle and especially working with JSON. I tried to use JSON_TABLE, but can't get that to work. What is an easy way to do this?

Request POST

{
    "Employees": [
        {
        "EMPLOYEE_ID": "100",
        "FIRST_NAME":"Steven",
        "LAST_NAME": "King",
        "HIRE_DATE": "17-JUN-03"
        },
        {
        "EMPLOYEE_ID": "101",
        "FIRST_NAME":"Neena",
        "LAST_NAME": "Kochar",
        "HIRE_DATE": "21-SEP-05"
        },
        {
        "EMPLOYEE_ID": "104",
        "FIRST_NAME":"Bruce",
        "LAST_NAME": "Ernst",
        "HIRE_DATE": "21-MAY-07"
        }
    ]
}

Response

{
    "Employees": [
        {
        "SALARY": "100000",
        "STATUS":"SUCCESS"      
        },
        {
        "SALARY": "100000",
        "STATUS":"SUCCESS"      
        },
        {
        "SALARY": "100000",
        "STATUS":"SUCCESS"      
        }
    ]
}

I tried something like the below query and get "column ambiguously defined" error at line 2 Column 8. I've tried some other variations of this, but I think I'm using JSON_TABLE wrong and maybe trying to do something that can't be done with JSON functions in Oracle 19c. I'm not sure of the best way to approach this and having trouble making sense of the Oracle documentation and articles. I'm also kind of new with APIs, but can easily do a simple GET request to this table with Dapper and return employee information in JSON.

SELECT *
FROM EMPLOYEES e
JOIN EMPLOYEES e ON e.EMPLOYEE_ID IN(
    SELECT jt.* FROM JSON_TABLE(
    '{
    "Payees": [
        {
        "EMPLOYEE_ID": "100",
        "FIRST_NAME":"Steven",
        "LAST_NAME": "King",
        "HIRE_DATE": "17-JUN-03"
        
        }
    ]
},
'COLUMNS(EMPLOYEE_ID VARCHAR2(20) PATH '$.EMPLOYEE_ID')) AS jt  
);

CodePudding user response：

Final solution: select e.salary FROM EMPLOYEES e WHERE e.EMPLOYEE_ID IN (SELECT jt.* FROM JSON_TABLE( q'~{ "Payees": [ { "EMPLOYEE_ID": "100", "FIRST_NAME":"Steven", "LAST_NAME": "King", "HIRE_DATE": "17-JUN-03" } ] }~', '$.Payees[*]' COLUMNS(EMPLOYEE_ID VARCHAR2(20) PATH '$.EMPLOYEE_ID')) AS jt) ;