0

We are seeing an issue with Oracle 19c and a JSON search index.

Given:

  • A table GEO containing a column JSON_DATA with JSON data and an IS_JSON constraint
  • An index created like this: create search index IX_GEO_SEARCH_INDEX on GEO (JSON_DATA) for json;
  • The GEO contains geological data, the JSON_DATA contains geological hierarchies. So one row in GEO could be a city, and the JSON_DATA column would contain a document with zip codes belonging to that city (simplified view, it contains more data):
{"zipCodes": [{"name": "12345"
               "valid": "true"}, 
               {"name": "12346"
               "valid": "true"}]}

The JSON structure is a fixed part of the application, we can not break it up into a relational schema.

Problem:

We often use the hierarchies from the JSON document to query for children, e.g. we want to know all cities for the zipCode "12345". To achieve this we create a query joining the zip codes from the table via JSON_TABLE:

     SELECT 
            *
        FROM 
            GEO g, 
            JSON_TABLE ( g.JSON_DATA, '$.zipCodes[*]' 
                    COLUMNS ( 
                        zip_name PATH '$.name' NULL ON ERROR, 
                        zip_valid PATH '$.valid' NULL ON ERROR
                    ) 
                ) 
            json_tbl 
        WHERE 
            json_tbl.zip_name = '12345'
        AND 
            json_tbl.zip_valid = 'true';

As this table contains ~20 million entries, we added a JSON Search Index to it, as described above. Sporadically we reach a point, where the query mentioned above does not work anymore. It will start working again, if we rebuild the Search Index.

Also, the following query, replacing '=' with 'LIKE' will work always, no matter if the index was rebuilt or not:

     SELECT 
            *
        FROM 
            GEO g, 
            JSON_TABLE ( g.JSON_DATA, '$.zipCodes[*]' 
                    COLUMNS ( 
                        zip_name PATH '$.name' NULL ON ERROR, 
                        zip_valid PATH '$.valid' NULL ON ERROR
                    ) 
                ) 
            json_tbl 
        WHERE 
            json_tbl.zip_name like '12345'
        AND 
            json_tbl.zip_valid like 'true';

Obviously there is an error in our design. Rebuilding the Index periodically is only a workaround, not a solution. Also, having an issue that renders our applications queries suddenly useless is a huge issue. I also don't understand, how the difference in behavior between equals and like happens. Do you have any idea what happens here, or some advice to fix it?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.