Add JSON object comparision

lwasylow

Since JSON is gaining a popularity and since Oracle 12.2 we got a proper support of JSON in database I thought it would b a good idea to implement a JSON comparison matcher.

I've did some initial work for cursor equality and will be adding a more methods. Here is a sample test and results.

create or replace PACKAGE ut_sample_test IS

   --%suite(Sample Test Suite)

   --%test(Compare Json)
   PROCEDURE ut_json;

END ut_sample_test;
/
create or replace PACKAGE BODY ut_sample_test IS

   PROCEDURE ut_json IS
    l_expected json_element_t;
    l_actual   json_element_t;
  begin
    -- Arrange
    l_expected := json_element_t.parse('{
    "Aidan Gillen": {
        "aboolean": true,
        "array": [
            "Game of Thron\"es",
            "The Wire"
        ],
        "boolean": true,
        "int": 2,
        "object": {
            "foo": "bar",
            "object1": {
                "new prop1": "new prop value"
            },
            "object2": {
                "new prop1": "new prop value"
            },
            "object3": {
                "new prop1": "new prop value"
            },
            "object4": {
                "new prop1": "new prop value"
            }
        },
        "string": "some string"
    },
    "Alexander Skarsgard": [
        "Generation Kill",
        "True Blood"
    ],
    "Amy Ryan": {
        "one": "In Treatment",
        "two": "The Wire"
    },
    "Annie Fitzgerald": [
        "Big Love",
        "True Blood"
    ],
    "Anwan Glover": [
        "Treme",
        "The Wire"
    ],
    "Clarke Peters": null
}');
    l_actual   := json_element_t.parse('{
    "Aidan Gillen": {
        "aboolean": "true",
        "array": [
            "Game of Thrones",
            "The Wire"
        ],
        "boolean": false,
        "int": "2",
        "object": {
            "foo": "bar"
        },
        "otherint": 4,
        "string": "some string"
    },
    "Alexander Skarsg?rd": [
        "Generation Kill",
        "True Blood"
    ],
    "Alice Farmer": [
        "The Corner",
        "Oz",
        "The Wire"
    ],
    "Amy Ryan": [
        "In Treatment",
        "The Wire"
    ],
    "Annie Fitzgerald": [
        "True Blood",
        "Big Love",
        "The Sopranos",
        "Oz"
    ],
    "Anwan Glover": [
        "Treme",
        "The Wire"
    ]
}
');

    --Act
    ut3.ut.expect( l_actual ).to_equal( l_expected );
   END;

END ut_sample_test;
/

Running matcher:

select * from table(ut.run('ut_sample_test'));

gives:

Sample Test Suite
  Compare Ref Cursors [.122 sec] (FAILED - 1)
 
Failures:
 
      Actual: json was expected to equal: json
      Diff:
      Found: 18 differences
      3 incorrect types,4 unequal values,11 missing properties
      Extra property 'Alexander Skarsg?rd' on path :$.Alexander Skarsg?rd
      Missing property 'Alexander Skarsgard' on path :$.Alexander Skarsgard
      Extra property 'Alice Farmer' on path :$.Alice Farmer
      Actual type is 'object' was expected to be 'array' on path :$.Amy Ryan
      Missing property 'Clarke Peters' on path :$.Clarke Peters
      Actual value is 'Big Love' was expected to be 'True Blood' on path :$.Annie Fitzgerald[0]
      Actual value is 'True Blood' was expected to be 'Big Love' on path :$.Annie Fitzgerald[1]
      Actual type is 'boolean' was expected to be 'string' on path :$.Aidan Gillen.aboolean
      Extra property '"The Sopranos"' on path :$.Annie Fitzgerald[2]
      Extra property '"Oz"' on path :$.Annie Fitzgerald[3]
      Actual value is 'true' was expected to be 'false' on path :$.Aidan Gillen.boolean
      Actual type is 'number' was expected to be 'string' on path :$.Aidan Gillen.int
      Extra property 'otherint' on path :$.Aidan Gillen.otherint
      Actual value is 'Game of Thron"es' was expected to be 'Game of Thrones' on path :$.Aidan Gillen.array[0]
      Missing property 'object1' on path :$.Aidan Gillen.object.object1
      Missing property 'object2' on path :$.Aidan Gillen.object.object2
      Missing property 'object3' on path :$.Aidan Gillen.object.object3
      Missing property 'object4' on path :$.Aidan Gillen.object.object4
      at "UT3.UT_SAMPLE_TEST.UT_JSON", line 94 ut3.ut.expect( l_actual ).to_equal( l_expected );
      
       
Finished in .147073 seconds
1 tests, 1 failed, 0 errored, 0 disabled, 0 warning(s)

I will be adding a more comparison method so far I think about adding

have_count(json_path)
*For a scalar, it returns 1.
*For an object, it returns the number of keys.
*For an array, it returns the number of items.
include(a_json_path)
exclude(a_json_path)
is_null
is_empty

Optionally maybe we could add some option to limit part e.g.
ut.expect(a_json).extractjson(json_path).to_equal(e_json).extractjson(different_json_path)

This is not replacement to include / exclude because each part can have a different extract paths.

If you have any comments or a good resources with a samples complex json to run through tests would be appreciated.