I’m trying to get the rows whose Fields.FieldId is in a list of numbers (eg: 7942 to 7950) or whose Fields.ParentFieldReference is in the same list of numbers but in a string format (eg: “7942”, …, “7950”).

I tried the following but I’m only getting rows that meet the FieldId condition and not the ParentFieldReference condition as well:

SELECT c.ID, f.FieldId, f.FieldValue, f.ParentFieldReference FROM c
JOIN f IN c.Fields
WHERE c.ID = '99999998_1_1_039075'
AND (
    f.FieldId IN (7942, 7943, 7944, 7945, 7946, 7947, 7948, 7949, 7950, 7951, 8305)
    OR ARRAY_CONTAINS(['7942', '7943', '7944', '7945', '7946', '7947', '7948', '7949', '7950', '7951', '8305'], f.ParentFieldReference, true)
)

I have tried using f.ParentFieldReference IN (list) but I’m still getting the same result. Does anyone know where I went wrong here?

Sample data as follows:

{
"ID": "99999998_1_1_039075",
"Fields": [
    {
        "FieldId": 79421,
        "FieldValue": "...",
        "ParentFieldReference": "7942"
    }
]
},
{
"ID": "99999998_1_1_039075",
"Fields": [
    {
        "FieldId": 7943,
        "FieldValue": "...",
        "ParentFieldReference": null
    }
]
}