Check accuracy of JSON string

Laury

    Hi,

     

    A JSON string can be defined as varchar2, clob for instance.

    Does someone know how to check if a given JSON is valid or not before any insertion to a table, or before processing it "on the fly", with pre-insertion?

    I didn't find such a function.

    It applied to Oracle RDBMS 12.1 and 12.2.

     

    Thanks by advance for any tip.

     

    Kind Regards

      • 1. Re: Check accuracy of JSON string
        Gaz in Oz

        One way is to use an "IS JSON" constraint on the table column you are inserting the json data into.

        Available in Oracle 12.1.0.2.0 and above

        https://docs.oracle.com/database/121/ADXDB/json.htm#ADXDB6246

        Example taken from that document:

        CREATE TABLE j_purchaseorder (

           id RAW (16) NOT NULL,

           date_loaded TIMESTAMP(6) WITH TIME ZONE,

           po_document CLOB

           CONSTRAINT ensure_json CHECK (po_document IS JSON)

        );

        • 2. Re: Check accuracy of JSON string
          Laury

          Hi Gaz,

          Yes, this it works because you define a JSON constraint on a pre-created table.

          Performing a JSON string insertion will indeed generate an error if the JSON string is uncorrect.

          But what if I want to check it "on the fly" in a PL/SQL procedure for instance?

          Kind Regards

          • 3. Re: Check accuracy of JSON string
            Gaz in Oz

            Use a staging table with an "IS JSON" constraintand try inserting into that would be the simplest option.

            • 4. Re: Check accuracy of JSON string
              Laury

              What do you mean by "staging table"?

              • 5. Re: Check accuracy of JSON string
                Gaz in Oz

                A dummy table whose sole purpose is to see whether the JSON document is valid. Don't commit any data, just try and insert it. If it fails, you know your json doc is invalid. You can then take appropriate action.

                That is if inserting it into the final table is not acceptable for what ever reason you have.

                • 6. Re: Check accuracy of JSON string
                  Gaz in Oz

                  ...if you have access to APEX 5+ in the database, then you can use the APEX_JSON package. Speifically APEX_JSON.PARSE()

                  https://stackoverflow.com/questions/40631688/how-can-i-parse-json-string-in-pl-sql

                  • 7. Re: Check accuracy of JSON string
                    Laury

                    Hi,

                    But using a "A dummy table" is the same as what you posted before.

                    Refering to the links you posted, can you tell me how APEX_JSON will check if the JSON string is valid or not?

                    I do not see any clear example in the documentation (that I have seen before).

                    Using the second link, can you tell me where such a JSON check is done?'

                    Kind Regards

                    • 8. Re: Check accuracy of JSON string
                      Gaz in Oz

                      Hi,

                      But using a "A dummy table" is the same as what you posted before.

                      You asked me what I meant so I expanded on my explanation.For 12.1,12.2 it really is the simplest way to parse the json to see if it is valid.

                      Create the table,write your PL/SQL. as a parsing step, insert your json document into a table with an "IS JSON" constraint. If it passes, the json got parsed successfully. If it fails, the json is not valid json. In either outcome, do stuff in your PL/SQL code to handle it.

                      Refering to the links you posted, can you tell me how APEX_JSON will check if the JSON string is valid or not?

                      APEX_JSON.PARSE(). [Edit: Ah, I have made an assumption. By passing it a json document and it can not assign all the json data, it will error. Signature 2.]

                      I do not see any clear example in the documentation (that I have seen before).

                      Using the second link, can you tell me where such a JSON check is done?'

                      About half way down, the post beginning:

                      With 11.0.4 version (there is no 11.0.4 version, of course) you have at least two choices(apart from writing

                      a parser yourself):

                      Depending on the version of RDBMS you are using, here are a couple of options: 

                      First one: for Oracle  11.1.0.7 and up, install Apex 5 and use apex_json package:

                      • 9. Re: Check accuracy of JSON string
                        MaxOrgiyan-Oracle

                        To add to other replies...

                         

                        You can use 'is json' in a where clause as opposed to in a constraint

                        (it's a condition, so using it in a constraint is just one option):

                         

                        select 1 from dual where '{"name" : "Laury"}' is json;

                         

                        In 12.2.0.1 and above there are built-in PLSQL object types for JSON.

                        They come pre-installed with Oracle Database, so you can use them right out of the box.

                        You can parse the JSON with them as follows.

                         

                        DECLARE

                          je JSON_ELEMENT_T;

                        BEGIN

                          je := JSON_ELEMENT_T.parse('{"name":"Laury"}');

                        END;

                         

                        If the JSON is bad, then it will return:

                         

                        ERROR at line 1:

                        ORA-40441: JSON syntax error

                         

                        But these object types are not available in 12.1.0.2.

                        • 10. Re: Check accuracy of JSON string
                          Gaz in Oz

                          ...so taking MaxOrgiyan-Oracle's "WHERE ...IS JSON" idea and incorporating that into a PL/SQL example:

                          SQL> ed

                          Wrote file afiedt.buf

                           

                            1  declare

                            2     json_clob CLOB := '{"name": "Laury"}';

                            3  begin

                            4     for j in(select 'It''s JSON.' text

                            5              from   dual

                            6              where  json_clob is json) loop

                            7        dbms_output.put_line(j.text);

                            8     end loop;

                            9* end;

                          SQL> set serverout on

                          SQL> /

                          It's JSON.

                           

                          PL/SQL procedure successfully completed.

                           

                          SQL>

                          1 位用户发现它有用
                          • 11. Re: Check accuracy of JSON string
                            Beda Hammerschmidt-Oracle

                            Depending which version of the database you use you can use either of these:

                             

                            begin

                            if '{a:1}' IS JSON then

                               dbms_output.put_line('Json it is');

                            end if;

                            end;

                            /

                             

                             

                            declare

                              result number;

                            begin

                              select 1 into result from dual where '{a:1}' is json;

                              if (result = 1) then

                                dbms_output.put_line('Json it is');

                              end if;

                            end;

                            /

                             

                             

                            IS JSON is the best and fastest way to check an input string for correct json syntax

                             

                            IS JSON (STRICT) will also check for correct quotes, number formats, etc.

                            For instance {a:1} fails the STRICT test, it needs to be {"a":1}

                             

                            IS JSON(WITH UNIQUE KEYS)  makes sure you have no duplicate keys, e.g. {a:1, a:2}

                             

                            you can combine both:

                             

                            begin

                            if '{a:1, a:2}' IS JSON (STRICT WITH UNIQUE KEYS) then

                               dbms_output.put_line('Json it is');

                            end if;

                            end;

                            /

                            1 位用户发现它有用
                            • 12. Re: Check accuracy of JSON string
                              Laury

                              Hi Gaz,

                               

                              As I just said, creating a "dummy table" works because you define a JSON check constraint on it.
                              But you need to create - first - the table (yet an easy way to check it)... I wish to perform that check "on the fly".

                              The APEX_JSON.PARSE does not really check for the validity of the "whole" JSON.
                              If I well undersoofd, it will return an error when it parses de JSON-string (either clob or varchar2) for an element of the JSON.
                              Using this package is OK if I know the format of the JSON string. But what if I do not know that format? Thus for a "global" check?

                               

                              Kind Regards

                              • 13. Re: Check accuracy of JSON string
                                Gaz in Oz

                                begin

                                if '{a:1}' IS JSON then

                                  dbms_output.put_line('Json it is');

                                end if;

                                end;

                                /

                                 

                                 

                                declare

                                  result number;

                                begin

                                  select 1 into result from dual where '{a:1}' is json;

                                  if (result = 1) then

                                    dbms_output.put_line('Json it is');

                                  end if;

                                end;

                                /

                                 

                                 

                                IS JSON is the best and fastest way to check an input string for correct json syntax

                                 

                                IS JSON (STRICT) will also check for correct quotes, number formats, etc.

                                For instance {a:1} fails the STRICT test, it needs to be {"a":1}

                                 

                                IS JSON(WITH UNIQUE KEYS)  makes sure you have no duplicate keys, e.g. {a:1, a:2}

                                 

                                you can combine both:

                                 

                                begin

                                if '{a:1, a:2}' IS JSON (STRICT WITH UNIQUE KEYS) then

                                  dbms_output.put_line('Json it is');

                                end if;

                                end;

                                /

                                Indeed. Refer to the 12.1 Oracle documentation I linked to. As OP stipulates "12.1 and 12.2" then the direct comparison in 12.1 will NOT work an so is useless for OP's requirements.

                                SQL> ed

                                Wrote file afiedt.buf

                                 

                                  1  begin

                                  2  if '{a:1}' IS JSON then

                                  3     dbms_output.put_line('Json it is');

                                  4  end if;

                                  5* end;

                                  6  /

                                if '{a:1}' IS JSON then

                                              *

                                ERROR at line 2:

                                ORA-06550: line 2, column 15:

                                PLS-00103: Encountered the symbol "JSON" when expecting one of the following:

                                not null of nan infinite dangling a empty

                                • 14. Re: Check accuracy of JSON string
                                  Laury

                                  Hi Beda, Gaz,

                                   

                                  The last posts crosses with each other.
                                  I will come back to your last suggestions.

                                   

                                  Kind Regards

                                  1 2 上一个 下一个