2 Replies Latest reply on Aug 30, 2018 12:15 PM by Erik Raetz

    JSON in the DB

    960754

      Seems to be a lot of discussion about moving data services out of oracle for OO data like JSON into NOSQL databases ......... Im kind of curious to look at the capabilities of JSON localized within the DB.

      One of the main "gripes" I have with JSON data/NOSQL is that if its not self contained (data has to end up in a relation system for other purposes) then why not store it in the DB and leverage capabilities of both being able to be queried directly and maintaining 1 system rather than 2 and have the benefits of joining directly to JSON data nad vice versa if required.

      So ..... going to do a few POC.

       

      Have a main question for today ..... around data change capture.

       

      If I [for lack of a better term] aggregate my relational data into Object notation like JSON ...... and store than DATA in JSON format within the DB ............ and then use JSON as the caching/DOC/Schemaless interface to my app ........... if I change the the JSON and/or the base data within the relational tables ...... whats the best way to keep that in sync ?

       

      Thinking basically triggers ...... or some posted event into a custom table ......... but anyone looked into this ?

       

      Example might be.

       

      I have all customer data in customer table and all order data in orders table and I join those to get all the relevant customer and order information since company was created - say 20 years of orders. That query can be somewhat expensive depending on data model.

      What if

       

      1) I create a JSON summary document of the customer and all their orders and store it in JSON.

      2) Application just comes and gets customerID= 1234. Gets the current customer info and all the data - no large SQL or join. And fast.

       

      But ...... Customer changes Surname due to marriage. Maybe the applicaiton updates the JSON document direct. How to propagate that directly to the relation CUSTOMERS table automatically.

      or more likely

      Another backend process makes a change to the orders table to say - hey the product shipped. How to update the JSON data for "Customer" experience data.

       

      etc

      Obviously it can be done programatically - i.e if you update the order go and also update the customer data JSON - but if it can be handled through CDC - then cuts down the effort significantly.

       

      thanks

        • 1. Re: JSON in the DB
          odie_63

          960754 wrote:

           

          What if

           

          1) I create a JSON summary document of the customer and all their orders and store it in JSON.

          2) Application just comes and gets customerID= 1234. Gets the current customer info and all the data - no large SQL or join. And fast.

           

          But ...... Customer changes Surname due to marriage. Maybe the applicaiton updates the JSON document direct. How to propagate that directly to the relation CUSTOMERS table automatically.

          or more likely

          Another backend process makes a change to the orders table to say - hey the product shipped. How to update the JSON data for "Customer" experience data.

           

          etc

          Obviously it can be done programatically - i.e if you update the order go and also update the customer data JSON - but if it can be handled through CDC - then cuts down the effort significantly.

          I would just use a view, or materialized view (if necessary).

           

          For example, to expose HR.DEPARTMENTS data, along with EMPLOYEES details :

          create or replace view departments_json_v (
            department_id
          , json_doc
          )
          as 
          select d.department_id
               , json_object(
                   'id' value d.department_id
                 , 'name' value d.department_name
                 , 'manager' value d.manager_id
                 , 'employees' value (
                     select json_arrayagg(
                              json_object(
                                'id' value e.employee_id
                              , 'firstname' value e.first_name
                              , 'lastname' value e.last_name
                              , 'hiredate' value e.hire_date
                              , 'salary' value e.salary
                              )
                              order by e.last_name
                              returning clob
                            )
                     from hr.employees e
                     where e.department_id = d.department_id
                   )
                   absent on null
                   returning clob
                 )
          from hr.departments d
          ;