$regex only works if All database columns have a value

[Vinny B]

Hello I am using the following code to search appery database.

return '{"$and" : [{"OldID": {"$regex":"^' + Appery("searchOldId").val() + '","$options":"i"}},{"L1": {"$regex":"^' + Appery("searchL1").val() + '","$options":"i"}},{"L2": {"$regex":"^' + Appery("searchL2").val() + '","$options":"i"}},{"L3": {"$regex":"^' + Appery("searchL3").val() + '","$options":"i"}},{"L4": {"$regex":"^' + Appery("searchL4").val() + '","$options":"i"}},{"L5": {"$regex":"^' + Appery("searchL5").val() + '","$options":"i"}},{"L6": {"$regex":"^' + Appery("searchL6").val() + '","$options":"i"}},{"Names": {"$regex":"^' + Appery("searchNames").val() + '","$options":"i"}}]}';

If a row has a value in every cell then that row will be searched.

If any row has one blank cell then that ROW will not be searched.

I need the above code to search the entire database no matter if a cell has data or not.

In the below image L4 is blank so If I search 3290 NOTHING WILL BE RETURNED. Which is wrong because 3290 exists.

In the below image L4 has data so if I search 3290 then the correct result will be returned.

I need the above code to search the entire database no matter if the cell has data or not.

[Serhii Kulibaba]

Hello Vinny,

Please add a query for getting empty columns:
pre{"L1":{"$not":{"$regex":"^"}}}/pre
So your query for each column should be like:
pre{"$or":[{"L1":{"$not":{"$regex": "^"} }}, {"L1":"^' + Appery("searchL1").val() + '","$options":"i"}]}/pre

[Vinny B]

i tried your suggestion but i get an error or nothing in return. can you write it using column L1 and L2? so i can make sure i am not doing it wrong.

thank you

[Serhii Kulibaba]

Please look at this example: pre
{"$and":[
{"$or":[
{"L1":{"$not":{"$regex": "^"} }},
{"L1":"^' + Appery("searchL1").val() + '","$options":"i"}
]},
{"$or":[
{"L2":{"$not":{"$regex": "^"} }},
{"L2":"^' + Appery("searchL2").val() + '","$options":"i"}
]}
]}/pre

[Vinny B]

That code does not work.. I get 400 bad request.

I tried this

return '{"$and":[{"$or":[{"L1":{"$not":{"$regex": "^"} }}, {"L1":"^' + Appery("searchL1").val() + '","$options":"i"}]}, {"$or":[{"L2":{"$not":{"$regex": "^"} }}, {"L2":"^' + Appery("searchL2").val() + '","$options":"i"}]}]}';

[Serhii Kulibaba]

Please use JS below, without any change:
prereturn {"$and":[
{"$or":[
{"L1":{"$not":{"$regex": "^"} }},
{"L1":{"$regex":"^" + Apperyio("searchL1").val(),"$options":"i"}}
]},
{"$or":[
{"L2":{"$not":{"$regex": "^"} }},
{"L2":{"$regex": "^" + Apperyio("searchL2").val(),"$options":"i"}}
]}
]};/pre