Improve Handling of IN Condition with JSON Data

[otaviojava]

I encountered an issue using the IN condition in SQL queries with JSON data in our database. The problem arises when the JSON field is compared to an array using the IN operator. Currently, it requires explicit declaration of the array with [], and if not done correctly, it leads to a type mismatch error.

• Table Structure:

  CREATE TABLE if not exists database (id STRING, entity STRING, content JSON, primary key (id))

• Sample JSON Data Inserted:

  {"name":"Lucas","location":"BR","type":"V","age":22,"entity":"person"}

Current Query:

select * from database WHERE database.entity= 'person' AND   database.content.location  IN  ?[]  AND  database.content.type  =  ?

Issue Description:
When using the IN condition in the provided query, it is required to declare the variable as an array using explicitly ?[]. Without this declaration, a type mismatch error occurs. This behavior differs from other NoSQL databases and Java drivers, where the system intelligently handles the array comparison without explicit declaration.

Suggested Enhancement:

I suggest enhancing the database system to recognize when the value being compared against automatically is an array and handle it without the need for explicit declaration or conversion. It would improve the developer experience and align the behavior with industry standards and user expectations.

Expected Behavior:

-- The enhanced query behavior should allow the following query without explicit array declaration.
select * from database WHERE database.entity= 'person' AND   database.content.location  IN  ? AND  database.content.type  =  ?

Impact:
This enhancement would make working with JSON data in the database more user-friendly and improve the developer experience.

[dario-vega]

Hi @otaviojava

The [] is the unbox operator to transform your array in a sequence. Read this to understand the difference between an array and a sequence.

For a better explanation, let me use this syntax - see locs is declared as an array. So the [] is something else.

DECLARE $locs ARRAY(JSON);  $type INTEGER;
select * from database 
WHERE database.entity= 'person' 
AND   database.content.location  IN  $locs[]  
AND  database.content.type  =  $type

With Oracle NoSQL, you can compare JSON atomics but you can also compare arrays and maps or combinations. You can do powerful queries.

Without using the unbox operator, the following command will compare 2 arrays, the stored value in an array field database.content.array with the constant array [1,2]. It does not compare the field database.content.array with the elements of the constant array (RHS), the values 1 and 2.

sql-> select database.content.array from database where database.content.array in [1,2]

{"array":[1,2]}

You could also want to do your IN against an array containing arrays [ [1,2], [1,2,3] ]
When providing this array, you have 2 possibilities

1. database.content.array = [1,2] or database.content.array = [1,2,3]
2. database.content.array = [[1,2], [1,2,3]]

Without using the unbox operator, you are in case (2)

sql-> select * from database where database.content.array in [[1,2], [1,2,3]];

0 row returned

In my database, I don't have a value with  {"array":[[1,2], [1,2,3]]}

By using the unbox operator, I can say that I want to compare against each element of the array - case (1) - [1,2] and [1,2,3]

sql-> select * from database where database.content.array in [[1,2], [1,2,3] ] [];

{"array":[1,2]}

But If you try to compare a JSON atomic field with an array, you will have an error because you cannot compare a string value and an array, e.g

LHS type: STRING RHS type: ARRAY
LHS val = BR RHS val = ["BR","FR"]

Note: maybe we can safely ignore this error because an array value ["BR","FR"] is not equal to a string value BR

Conclusion

Agree, it is not standard SQL. Currently, we are promoting the flexibility of this approach for a NoSQL database vs years of standard SQL for relational databases. It allows us to work with JSON complex documents in a user-friendly way and improves the developer experience. But it seems that it complexifies 🤔 the understanding of basic queries like database.content.location IN ? when the developer expects standard SQL behavior. In our case, he needs to write database.content.location IN ?[] to unbox the array.

Currently, there is no implicit unboxing of arrays. Hope that my explanation helps you to understand why.

You can read the documentation for the IN operator here for more details.

We will discuss this internally.