DZone
Thanks for visiting DZone today,
Edit Profile
  • Manage Email Subscriptions
  • How to Post to DZone
  • Article Submission Guidelines
Sign Out View Profile
  • Post an Article
  • Manage My Drafts
Over 2 million developers have joined DZone.
Log In / Join
Refcards Trend Reports Events Over 2 million developers have joined DZone. Join Today! Thanks for visiting DZone today,
Edit Profile Manage Email Subscriptions Moderation Admin Console How to Post to DZone Article Submission Guidelines
View Profile
Sign Out
Refcards
Trend Reports
Events
Zones
Culture and Methodologies Agile Career Development Methodologies Team Management
Data Engineering AI/ML Big Data Data Databases IoT
Software Design and Architecture Cloud Architecture Containers Integration Microservices Performance Security
Coding Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks
Culture and Methodologies
Agile Career Development Methodologies Team Management
Data Engineering
AI/ML Big Data Data Databases IoT
Software Design and Architecture
Cloud Architecture Containers Integration Microservices Performance Security
Coding
Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance
Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks
  1. DZone
  2. Data Engineering
  3. Databases
  4. Relational to JSON With SQL

Relational to JSON With SQL

With Oracle's move to offer JSON support for its database, let's look at generating, exploring, and processing JSON data.

Dan McGhan user avatar by
Dan McGhan
·
Oct. 14, 18 · Tutorial
Like (2)
Save
Tweet
Share
8.12K Views

Join the DZone community and get the full member experience.

Join For Free

Oracle started adding JSON support to Oracle Database with version 12.1.0.2. The earliest support was targeted at storing, indexing, and querying JSON data. Version 12.2 rounded out that support by adding features for generating, exploring, and processing JSON data. See the JSON Developer’s Guide for a comprehensive overview of what’s now available. In this post, I’ll leverage the new SQL operators for JSON generation to convert the relational data to meet the goal.

Please Note: This post is part of a series on generating JSON from relational data in Oracle Database. See that post for details on the solution implemented below as well as other options that can be used to achieve that goal.

Solution

The 12.2+ SQL functions available for JSON generation are:

  • JSON_OBJECT – single-row function, creates an object for each row.
  • JSON_ARRAY – single-row function, creates an array for each row.
  • JSON_OBJECTAGG – aggregate function, creates an object based on groups of rows.
  • JSON_ARRAYAGG – aggregate function, creates an array based on groups of rows.

The following solution uses JSON_OBJECT and JSON_ARRAYAGG multiple times, nesting them as needed to produce the desired output.

select json_object(
  'id' is department_id,
  'name' is department_name,
  'location' is (
    select json_object(
      'id' is location_id,
      'streetAddress' is street_address,
      'postalCode' is postal_code,
      'country' is (
        select json_object(
          'id' is country_id,
          'name' is country_name,
          'regionId' is region_id 
        )
        from countries
        where country_id = loc.country_id
      )
    )
    from locations loc
    where location_id = dept.location_id
  ),
  'manager' is (
    select json_object(
      'id' is employee_id,
      'name' is first_name || ' ' || last_name,
      'salary' is salary,
      'job' is (
        select json_object(
          'id' is job_id,
          'title' is job_title,
          'minSalary' is min_salary,
          'maxSalary' is max_salary
        )
        from jobs
        where job_id = man.job_id
      )
    )
    from employees man
    where employee_id = dept.manager_id
  ),
  'employees' is (
    select json_arrayagg(
      json_object(
        'id' is employee_id,
        'name' is first_name || ' ' || last_name,
        'isSenior' is 
          case when emp.hire_date < to_date('01-01-2005', 'dd-mm-yyyy')
            then 'true'
            else 'false'
          end format json,
        'commissionPct' is commission_pct,
        'jobHistory' is (
          select json_arrayagg(
            json_object(
              'id' is job_id,
              'departmentId' is department_id,
              'startDate' is to_char(start_date, 'DD-MON-YYYY'),
              'endDate' is to_char(end_date, 'DD-MON-YYYY')
            )
          )
          from job_history
          where employee_id = emp.employee_id
        )
      )
    )
    from employees emp
    where department_id = dept.department_id
  )
) as department
from departments dept
where department_id = :department_id


As was the case with the SQL query used for ORDS, this is a fairly large query. But I love the control the new SQL operators provide! As an example, I’ve highlighted line 50, which uses the FORMAT JSON keywords to declare that the value is to be considered as JSON data. This allowed me to add Boolean values to the JSON output despite the fact that Oracle’s SQL engine doesn’t support Boolean. There are other optional keywords to modify the behavior of the JSON generating functions.

Output

I’m happy to report that the solution above generates JSON that meets the goal 100%!

{
  "id": 10,
  "name": "Administration",
  "location": {
    "id": 1700,
    "streetAddress": "2004 Charade Rd",
    "postalCode": "98199",
    "country": {
      "id": "US",
      "name": "United States of America",
      "regionId": 2
    }
  },
  "manager": {
    "id": 200,
    "name": "Jennifer Whalen",
    "salary": 4400,
    "job": {
      "id": "AD_ASST",
      "title": "Administration Assistant",
      "minSalary": 3000,
      "maxSalary": 6000
    }
  },
  "employees": [
    {
      "id": 200,
      "name": "Jennifer Whalen",
      "isSenior": true,
      "commissionPct": null,
      "jobHistory": [
        {
          "id": "AD_ASST",
          "departmentId": 90,
          "startDate": "17-SEP-1987",
          "endDate": "17-JUN-1993"
        },
        {
          "id": "AC_ACCOUNT",
          "departmentId": 90,
          "startDate": "01-JUL-1994",
          "endDate": "31-DEC-1998"
        }
      ]
    }
  ]
}



However, when I ran this query on department 60 (which as the most employees) I received this error: ORA-40459: output value too large (actual: 4071, maximum: 4000). This is because each of the JSON generation functions has a default output of varchar2(4000). This is fine for many use cases, but it’s easily exceeded with the aggregate functions and deeply nested structures.

The solution is to leverage the RETURNING clause to specify a different data type or size. See this gist to get an idea of how the solution above could be modified to use the RETURNING clause. In 12.2, there were some restrictions on which functions could work with CLOBs, but they’ve been lifted in 18c.

Summary

This is my favorite solution of the series – by far! The JSON generation functions are very powerful and because they’re in the database it’s possible to leverage them from just about anywhere, including Node.js and ORDS.

JSON sql Relational database

Published at DZone with permission of Dan McGhan, DZone MVB. See the original article here.

Opinions expressed by DZone contributors are their own.

Popular on DZone

  • Utilize OpenAI API to Extract Information From PDF Files
  • API Design Patterns Review
  • Building a Scalable Search Architecture
  • Microservices Discovery With Eureka

Comments

Partner Resources

X

ABOUT US

  • About DZone
  • Send feedback
  • Careers
  • Sitemap

ADVERTISE

  • Advertise with DZone

CONTRIBUTE ON DZONE

  • Article Submission Guidelines
  • Become a Contributor
  • Visit the Writers' Zone

LEGAL

  • Terms of Service
  • Privacy Policy

CONTACT US

  • 600 Park Offices Drive
  • Suite 300
  • Durham, NC 27709
  • support@dzone.com
  • +1 (919) 678-0300

Let's be friends: