Question 110876 in Json Question posted in
Our archive of expertly curated questions and answers provides insights and solutions to common problems related to this popular data interchange format. From parsing and manipulating JSON data to integrating it with various programming languages and web services, our archive has got you covered. Start exploring today and take your JSON skills to the next level

Oracle SQL or PL/SQL to get relational database values from JSON string

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.

Tags:

2

Answers


  1. 0

    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

    Login or Signup to reply.
  2. 0

    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
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search