Primary desire is to search for Tags associated with a document. But, I'd like for the results to use an index.
Prior question of mine, gave a nice SQL solution (Tubby) and code for ODCIIndex (Odie63).
Prior Post: Indexing a collection of TAGs?
I'm asking the question again because of new database features MULTIVALUE INDEX and the JSON data type.
Database: 21c ATP Free Tier
QUESTION
Is there a way to do TAG searching such that the MULTIVALUE INDEX is used?
Do the new 21c features help?
The SQL cannot be hard-coded. A "search list" will be provided (eg APEX Shuttle/multivalue LOV)
SETUP
drop table test_tags;
create table test_tags (
person varchar2(256) primary key,
tags json
);
insert into test_tags values ( 'alice', '{"tags":["a"]}' );
insert into test_tags values ( 'bob', '{"tags":["a","b","c"]}' );
insert into test_tags values ( 'carol', '{"tags":["b","c","d"]}' );
insert into test_tags values ( 'dave', '{"tags":["d","e","f"]}' );
insert into test_tags values ( 'emma', '{"tags":["a","b","c","d"]}' );
commit;
create multivalue index test_tags_ix1 on test_tags t ( t.tags.tags.string() );
ATTEMPT 1 - SINGLE VALUE
This uses the index but requires a hard coded search string
select *
from test_tags t
where json_exists( t.tags, '$.tags[*]?( @ == "a" )' );
ATTEMPT 2 - SUBMULTISET
This would be VERY Nice for use in APEX IR ( the WERE is actually a parameter for the IR).
However, it does not use the index.
select *
from test_tags t
where apex_string.split( 'a,b,c', ',')
submultiset json_value( t.tags, '$.tags' returning apex_t_varchar2 );
ATTEMPT 3 - SIMPLE SQL - this FAILS
If you take out the GROUP BY/HAVING lines, it works
SELECT t.person, t.tags
FROM TEST_TAGS t, json_table( t.tags, '$.tags[*]'
columns
tag_name varchar2(256) path '$.string()'
) z
join table( apex_string.split( 'a,b,c', ',' ) ) r on z.tag_name = r.column_value
group by t.person, t.tags
having count(*) = 3
Error:
ORA-40796: invalid comparison operation involving JSON type value
40796. 00000 - "invalid comparison operation involving JSON type value"
*Cause: An attempt was made to perform a comparison against a JavaScript
Object Notation (JSON) type value.
*Action: Add a JSON trailing type cast function to compare the JSON type
value against a scalar value. Use JSON_EQUAL to compare two JSON
type values.
EDIT : you can't GROUP BY on a JSON data type. After fix, the SQL is more complicated then I'd like and, more importantly, still doesn't use the index.
with data as (
SELECT t.person
FROM TEST_TAGS t, json_table( t.tags, '$.tags[*]'
columns
tag_name varchar2(256) path '$.string()'
) z
join table( apex_string.split( 'a,b,c', ',' ) ) r on z.tag_name = r.column_value
group by t.person
having count(*) = 3
)
select *
from data d join test_tags t using (person)
;
TIA