I'm using Oracle 19c and was wondering if you could take a JSON object like this...

{
  "Employees": [
    {
      "EMPLOYEE_NUMBER": "XXX-XX-XXXX",
      "FIRST_NAME": "John",
      "LAST_NAME": "Doe",
      "TAX_YEAR": "2022"
    },
    {
      "EMPLOYEE_NUMBER": "XXX-XX-XXXX",
      "FIRST_NAME": "John",
      "LAST_NAME": "Doe",
      "TAX_YEAR": "2021"
    }
  ]
}

And return something like the below from a relational database table by filtering on the Employee Number, First and Last Name, and a specific year.

[
  {
    "BASE_SALARY": "24000",
    "BONUS": "1200",
    "STATUS": "0"
  },
  {
    "BASE_SALARY": "17000",
    "BONUS": "0",
    "STATUS": "0"
  }
]

The status shows as 0 if everything comes back with no errors, but can have other numbers for specific issues. The status message isn't that important to me right now. I'm just having trouble getting the PL/SQL.

I have done something similar using JSON_TABLE, but only using one filter item, the EMPLOYEE_ID. I don't know how to go about it if you have to match other items in the filter (names, years, etc.)

The query below successfully returns the salary based on EMPLOYEE_ID. I don't know how to use something similar with more than one filter item.

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"
                },
                {
                "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"
                }
            ]
        }~', '$.Payees[*]' 
        COLUMNS(
                employee_id VARCHAR2(20) PATH '$.EMPLOYEE_ID'       
               )) jt)

This is assuming a relational table called EMPLOYEES with employee number, names, salary and bonus information for each year that they worked for the company.

CodePudding user response：

You can put, as an example, such a list

(e.employee_id, e.first_name, TO_CHAR(hire_date,'DD-MON-YY'))

before IN operator

along with adding the respective columns (first_name,hire_date) to the COLUMNS list of JSON_TABLE() such as

SELECT e.salary
  FROM employees e
 WHERE (e.employee_id, e.first_name, TO_CHAR(hire_date,'DD-MON-YY')) IN
       (SELECT jt.* 
          FROM JSON_TABLE( q'~{
            "Payees": [
                {
                "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"
                }
            ]
        }~', '$.Payees[*]' 
        COLUMNS(
                employee_id VARCHAR2(20) PATH '$.EMPLOYEE_ID',
                first_name  VARCHAR2(50) PATH '$.FIRST_NAME',
                hire_date   VARCHAR2(50) PATH '$.HIRE_DATE'        
               )) jt)

Demo

CodePudding user response：

Use JSON_TABLE to turn all key-value pairs within the JSON into a table. Then, one can filter the table using conditions within the WHERE clause. JOIN the virtual JSON table to the employees table. Use JSON_OBJECT to make each row of data into a JSON object. Finally, use JSON_ARRAYAGG to gather the JSON objects into a single JSON array.

SELECT JSON_ARRAYAGG(JSON_OBJECT(KEY 'BASE_SALARY' VALUE e.base_salary
                                ,KEY 'BONUS' VALUE e.bonus
                                ,KEY 'STATUS' VALUE e.status) 
                     FORMAT JSON RETURNING CLOB
                    )
FROM   JSON_TABLE('{"Payees": [
                    {
                    "EMPLOYEE_ID": "100",
                    "FIRST_NAME":"Steven",
                    "LAST_NAME": "King",
                    "TAX_YEAR": "2020"
                    },
                    {
                    "EMPLOYEE_ID": "101",
                    "FIRST_NAME":"Neena",
                    "LAST_NAME": "Kochar",
                    "TAX_YEAR": "2021"
                    },
                    {
                    "EMPLOYEE_ID": "104",
                    "FIRST_NAME":"Bruce",
                    "LAST_NAME": "Ernst",
                    "TAX_YEAR": "2021"
                    }
                ]
            }'
                           ,'$.Payees[*]'
                           COLUMNS (employee_id VARCHAR2(20) PATH '$.EMPLOYEE_ID'
                                   ,first_name VARCHAR2(20) PATH '$.FIRST_NAME'
                                   ,last_name VARCHAR2(20) PATH '$.LAST_NAME'
                                   ,tax_year VARCHAR2(20) PATH '$.TAX_YEAR'
                                   )
                 ) jt
JOIN  employee e ON e.employee_id = jt.employee_id
WHERE jt.employee_id >= 100
AND   jt.tax_year IN (2020, 2021)
AND   jt.first_name LIKE '%e%'
AND   LENGTH(jt.last_name) <= 12