JSON Query where clause with Array

[S_Coskun]

Hi, I really struggle building the JSON query I need, that searches in a JSON array.
The array looks like this:

[
{"id":"weekly","timeWindow":{"to":1677874620000,"from":1677867420000}},
{"id":"weekly","timeWindow":{"to":1677961020000,"from":1677953820000}},
{"id":"weekly","timeWindow":{"to":1681050600000,"from":1681043400000}}
]

My goal is to check if there is a database entry that fulfills a condition like this with a given timestamp in milliseconds ts:
rules -> '$[*].timeWindow.from' >= ts
or
rules -> '$[*].timeWindow.from' = ts

Every time I use an explicit index like this '$[0].timeWindow.from' = ts the query works.

Thanks for any suggestions.
Best regards

[Viktor_Mudrevsky]

Hi @S_Coskun

Please provide a bit more additional information. It’s not entirely clear at the moment where and how you’re making these requests.

Regards,
Viktor

[S_Coskun]

Thanks for the quick response.
I want to use this in a codeless request where the parameters are “from” and “to” (both of type number as timestamps).
The database object “ParkingSpace” has the column “rules”. In this column is a json array with json objects, just like this:

[
{"id":"weekly","timeWindow":{"to":1677874620000,"from":1677867420000}},
{"id":"weekly","timeWindow":{"to":1677961020000,"from":1677953820000}},
{"id":"weekly","timeWindow":{"to":1681050600000,"from":1681043400000}}
]

In the codeless request I want to check the database, if there are parking spaces, that are available in the time span I pass through the parameters “from” and “to”. Therefore, my approach is to use json queries to solve this. My last attempt was like this:

jsonextract(rules, '$[*].timeWindow.from') <= from and jsonextract(rules, '$[*].timeWindow.to') >= to

But this isn’t working neither.
Please tell me, if you need more information.

Best regards

[Viktor_Mudrevsky]

Hi @S_Coskun

Unfortunately, in my test application, I couldn’t find a way to retrieve records from the “rules” column that have parking spaces that meet the conditions without specifying an index. I’m not sure if it’s even possible, as the JSON column is not a database - it’s just a field in the database.

Most likely, you need to split the data from this JSON column into tables, so that parking tickets are stored where they belong - in a separate table, and not hidden inside a JSON array. Then you can access them properly.

Could you please provide your App ID and tell me where I can see and try this in your application? Perhaps one of my colleagues will also try to help you or clarify how to solve this problem.

Regards,
Viktor

[S_Coskun]

Thanks,
the app id is E3028F3A-4EC2-1255-FFE2-920CBB2EF500.
I already store the tickets separately. The column rules determines rules when users can or cannot use a certain parking space.
You can try this with the ParkingSpaces table. And there you see the rules column.

Best regards,
Serkan

[Viktor_Mudrevsky]

Hi @S_Coskun

As a solution, I would suggest restructuring your data and creating a Rules table, for example like this:

and populate it with the same data you currently have in your JSON array.

Then, in the ParkingSpaces table, use the Data Object Relationship One to Many type (instead of the JSON type) for the ‘rules’ column and link it to the Rules table.
This way, you will be able to write any condition in your query and get the results you need.
For example:

My solution is justified because if your data in the JSON array has a structure, then it can be made into a proper table where each column will have its own data type and any queries can be made to this data.

Regards,
Viktor