3 Replies Latest reply on May 22, 2018 3:51 AM by Pierre Yotti

    USER_JSON_DATAGUIDES - DATAGUIDE column is empty

    Yeo Teck Huat

      We are trying to generate a DATAGUIDE with JSON file that contain array of objects,

      but encounter empty dataguide, do anyone have the same issue or know why this is happening?

       

      Thank you.

       

      Steps

      =====

       

      1) Uploaded a JSON file as attached into the table below

      CREATE TABLE test_3_workers

        (trans_id            NUMBER,

          load_date        DATE,

          filename           VARCHAR2(60),

         json_data         BLOB CONSTRAINT ensure_json_test CHECK (json_data IS JSON))

         LOB (json_data) STORE AS (CACHE);

       

      2) Create a Search INDEX

      CREATE SEARCH INDEX test_3_workers_only_idx ON test_3_workers(json_data) FOR JSON PARAMETERS ('SEARCH_ON NONE');

       

      3) Check the DATAGUIDE, but it's empty

      SQL> select table_name, column_name, (dbms_lob.getlength(DATAGUIDE))/1024/1024 from USER_JSON_DATAGU

      IDES;

      TABLE_NAME

      ----------------------------------------------------------------------------------------------------

      COLUMN_NAME

      ----------------------------------------------------------------------------------------------------

      (DBMS_LOB.GETLENGTH(DATAGUIDE))/1024/1024

      -----------------------------------------

      TEST_3_WORKERS

      JSON_DATA

                                              0

       

      NIE_WORKDAY

      JSON_DATA

                                     .122805595

        • 1. Re: USER_JSON_DATAGUIDES - DATAGUIDE column is empty
          Pierre Yotti

          Hi,

           

          in case to generate the Dataguide, you should have data in the Table.

           

          Example

           

           

          INSERT INTO test_3_workers (trans_id,filename, json_data)

          VALUES (SYS_GUID(),'aa',

                  '{

                    "FirstName"      : "John",

                    "LastName"       : "Doe",

                    "Job"            : "Clerk",

                    "Address"        : {

                                        "Street"   : "99 My Street",

                                        "City"     : "My City",

                                        "Country"  : "UK",

                                        "Postcode" : "A12 34B"

                                       },

                    "ContactDetails" : {

                                        "Email"    : "john.doe@example.com",

                                        "Phone"    : "44 123 123456",

                                        "Twitter"  : "@johndoe"

                                       },

                    "DateOfBirth"    : "01-JAN-1980",

                    "Active"         : true

                   }');

           

           

           

          SELECT JSON_DATAGUIDE(JSON_DATA) dg_doc

          FROM   test_3_workers;

           

           

          That give me that

           

          [

             {

                "o:path":"$.Job",

                "type":"string",

                "o:length":8

             },

             {

                "o:path":"$.Active",

                "type":"boolean",

                "o:length":4

             },

             {

                "o:path":"$.Address",

                "type":"object",

                "o:length":128

             },

             {

                "o:path":"$.Address.City",

                "type":"string",

                "o:length":8

             },

             {

                "o:path":"$.Address.Street",

                "type":"string",

                "o:length":16

             },

             {

                "o:path":"$.Address.Country",

                "type":"string",

                "o:length":2

             },

             {

                "o:path":"$.Address.Postcode",

                "type":"string",

                "o:length":8

             },

             {

                "o:path":"$.LastName",

                "type":"string",

                "o:length":4

             },

             {

                "o:path":"$.FirstName",

                "type":"string",

                "o:length":4

             },

             {

                "o:path":"$.DateOfBirth",

                "type":"string",

                "o:length":16

             },

             {

                "o:path":"$.ContactDetails",

                "type":"object",

                "o:length":128

             },

             {

                "o:path":"$.ContactDetails.Email",

                "type":"string",

                "o:length":32

             },

             {

                "o:path":"$.ContactDetails.Phone",

                "type":"string",

                "o:length":16

             },

             {

                "o:path":"$.ContactDetails.Twitter",

                "type":"string",

                "o:length":8

             }

          ]

           

          Have a look here

          https://oracle-base.com/articles/12c/json-data-guide-12cr2

          • 2. Re: USER_JSON_DATAGUIDES - DATAGUIDE column is empty
            Yeo Teck Huat

            Thanks Alli for your prompt reply.

             

            We have successful upload a JSON file, and was able to use the DataGuide.

             

            But with the JSON files that contain array of objects,

            it seems that the Oracle 12c DataGuide is not working.

             

            Example:

            =======

            This JSON has an array of 2 objects.

            I have verified that this JSON is a valid JSON data using Oracle recommended JSON checker

            https://jsonlint.com/

             

            {

            "Report_Entry": [{

               "Row_Sequence": "1",

               "Total_Base_Pay_Annualized_-_Amount": "213298",

               "Employee": "Logan McNeil",

               "Dependents": "Megan McNeil; Pat McNeil",

               "Management_Level": "4 Vice President",

               "businessTitle": "Vice President, Human Resources",

               "Cost_Center": "41600 HR Services",

               "Employee_ID": "21001",

               "isManager": "1",

               "location": "San Francisco",

               "Email_-_Primary_Work_or_Primary_Home": "lmcneil@workday.net",

               "Hire_Date": "2000-01-01"

            },

            {

               "Row_Sequence": "2",

               "Total_Base_Pay_Annualized_-_Amount": "333266",

               "Employee": "Steve Morgan",

               "Dependents": "Elizabeth Morgan; Josh Morgan; Catherine Morgan",

               "Management_Level": "2 Chief Executive Officer",

               "businessTitle": "Chief Executive Officer",

               "Cost_Center": "10000 Office of CEO",

               "Employee_ID": "21002",

               "isManager": "1",

               "location": "San Francisco",

               "Email_-_Primary_Work_or_Primary_Home": "smorgan@workday.net",

               "Hire_Date": "2000-01-01"

            }]

            }

            • 3. Re: USER_JSON_DATAGUIDES - DATAGUIDE column is empty
              Pierre Yotti

              Hi,

              it working for me

               

              SELECT JSON_DATAGUIDE(JSON_DATA) dg_doc

              FROM   test_3_workers;

               

              [{

                "o:path": "$.Report_Entry",

                "type": "array",

                "o:length": 1024

              }, {

                "o:path": "$.Report_Entry.Employee",

                "type": "string",

                "o:length": 16

              }, {

                "o:path": "$.Report_Entry.location",

                "type": "string",

                "o:length": 16

              }, {

                "o:path": "$.Report_Entry.Hire_Date",

                "type": "string",

                "o:length": 16

              }, {

                "o:path": "$.Report_Entry.isManager",

                "type": "string",

                "o:length": 1

              }, {

                "o:path": "$.Report_Entry.Dependents",

                "type": "string",

                "o:length": 64

              }, {

                "o:path": "$.Report_Entry.Cost_Center",

                "type": "string",

                "o:length": 32

              }, {

                "o:path": "$.Report_Entry.Employee_ID",

                "type": "string",

                "o:length": 8

              }, {

                "o:path": "$.Report_Entry.Row_Sequence",

                "type": "string",

                "o:length": 1

              }, {

                "o:path": "$.Report_Entry.businessTitle",

                "type": "string",

                "o:length": 32

              }, {

                "o:path": "$.Report_Entry.Management_Level",

                "type": "string",

                "o:length": 32

              }, {

                "o:path": "$.Report_Entry.\"Total_Base_Pay_Annualized_-_Amount\"",

                "type": "string",

                "o:length": 8

              }, {

                "o:path": "$.Report_Entry.\"Email_-_Primary_Work_or_Primary_Home\"",

                "type": "string",

                "o:length": 32

              }]

               

               

              Here is another example

              https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:9537440400346279780

               

              DATABASE VERSION

               

              Bildschirmfoto 2018-05-22 um 05.51.12.png