2 Replies Latest reply on Mar 24, 2019 1:57 AM by Gaz in Oz

    How can I update specific value for  JSON data in Oracle DB 12.1.0.2.0?

    Pierre Yotti

      Hi,

       

      i would like to update a specific value for JSON in Oracle Database 12.1.0.2.0

       

      - my Table

       

      CREATE TABLE json_documents (

        id    RAW(16) NOT NULL,

        data  CLOB,

        CONSTRAINT json_documents_pk PRIMARY KEY (id),

        CONSTRAINT json_documents_json_chk CHECK (data IS JSON));

       

      -- Insert Data

      INSERT INTO json_documents (id, data)

      VALUES (SYS_GUID(),

              '{

                "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

               }');

       

       

      INSERT INTO json_documents (id, data)

      VALUES (SYS_GUID(),

              '{

                "FirstName"      : "Jayne",

                "LastName"       : "Doe",

                "Job"            : "Manager",

                "Address"        : {

                                    "Street"   : "100 My Street",

                                    "City"     : "My City",

                                    "Country"  : "UK",

                                    "Postcode" : "A12 34B"

                                   },

                "ContactDetails" : {

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

                                    "Phone"    : ""

                                   },

                "DateOfBirth"    : "01-JAN-1982",

                "Active"         : false

               }');

       

      -- Update

      i get the error

      03001. 00000 -  "unimplemented feature"

      *Cause:    This feature is not implemented.

       

      When i try that update

       

      UPDATE json_documents  a

            SET a.data.FirstName = 'hallo'

            WHERE a.id= '84AC470415E5D439E0533137548C7634';

       

      Somebody can help please?

       

      Thanks

       

      Pierre