Background
App ID: 532CF1B7-693E-48CA-B1B1-F16FCB709609
(tl;dr at the bottom)
We’re building a CRM that allows users to add contacts and store properties on those contacts. Users create the property “types” (i.e. “City”, “Country”, “Favourite colour”, etc) which are then usable on all their contacts. As these properties can be varying data types like numbers or strings, we’ve designed our property tables to use JSON as the datatype for storing the values.
What we’re looking to do now is create a filtering system that users can set up on their list of contacts. In my app I’ve made extra tables to simulate what we’re trying to do:
‘EXAMPLE_Contact’ table:
(One contact has properties { “City”: “Toronto”, “Country”: “Canada” }, the other has { “City”: “Vancouver”, “Country”: “Canada” })
‘EXAMPLE_Properties’ table:
On the contact table, I can have a where clause set up to find contacts that live either in “city: Toronto” or “city: Vancouver”:
(properties.name = 'City' AND properties.value->'$[0]' = 'Toronto') OR (properties.name = 'City' AND properties.value->'$[0]' = 'Vancouver')
[
{
"___class": "EXAMPLE_Contact",
"ownerId": null,
"updated": null,
"created": 1644514424000,
"objectId": "9D2006B8-E5A0-40B6-864C-2C1C65027ED4"
},
{
"___class": "EXAMPLE_Contact",
"ownerId": null,
"updated": null,
"created": 1644514344000,
"objectId": "E63C7A7C-13DD-4B1A-BF35-DA571D096A07"
}
]
This works as expected, I can filter based on a city or - using OR logic - two cities, but I’m trying to figure out how I should structure the clause to support AND logic. For example:
(properties.name = 'Country' AND properties.value->'$[0]' = 'Canada') AND (properties.name = 'City' AND properties.value->'$[0]' = 'Toronto')
Returns:
[]
Which I understand doesn’t work as it’s trying to compare two of the same properties with different values which will always be false.
Question (TL;DR)
I’m wondering is there a way to write a where clause to perform AND logic on a 1:N related column, so that if any two related records satisfy the condition then the where clause passes for that record?
If not, I’m open for suggestions or workarounds to achieve this behaviour. I’m not much of a database expert so any advice is very highly appreciated!
Thank you!