oracle-database

Oracle database 19C, In SQL query how to compare '["a", "b"]' = '["b", "a"]' is TRUE

Oracle database;

--sample table
CREATE TABLE myTable (
    id     varchar2(5), 
    data  varchar2(255)
);

select  from myTable; --> table has these data
id  data    
1   ["a", "b"]
2   ["a", "b", "c"]
3   ["b", "a"]

Expecting: 1st and 3rd both records as it has matching values "a" and "b" (which will come as user parameter)

id  data    
1   ["a", "b"]
3   ["b", "a"]

dumb query:
select * from myTable 
where 
data = '["a", "b"]'; -- **WHAT WILL COME HERE**

-- This query will return 1st record, but need 3rd record as well!!

I tried JSON_CONTAINS, JSON_TEXTCONTAINS - but not helping here.

Solution

  • As you seem to have JSON arrays, you can use (or, possibly, abuse) the JSON functions.

    If you have or can create a collection type such as:

    create type t_string as table of varchar2(1)
/

    then you can use the json_value function specifying the collection type as the return type, both for the column value and

    json_value(data, '$' returning t_string)

    and your comparison value

    json_value('["a", "b"]', '$' returning t_string)

    and then compare those collections for equality:

    select *
from myTable
where json_value(data, '$' returning t_string) = json_value('["a", "b"]', '$' returning t_string)
    ID DATA
    1 ["a", "b"]
    3 ["b", "a"]

    fiddle