Forum Discussion
MongoDB Atlas SQL connection using 2 connections with 1 data gateway problem
Hi Anonymous
I contacted Microsoft support and they were helping me, but finally I managed to find a solution with my colleagues. It turns out that it is possible to create a kind of ‘view’ in a section that Mongo Atlas offers called Data Federation. There I put together both collections, each one from a different cluster and I only needed one connection for both Desktop and Service.
So as this has helped me, I'll give this answer as a solution.
Anyway, thank you very much for the help provided in your comments!
Error 1007 indicates that the status field does not exist in all objects in the quotations array, or that quotations is null or empty in some documents.
To avoid this, make sure the field exists before accessing it. For example:
IF(quotations IS NOT NULL AND quotations.status IS NOT NULL, quotations.status, NULL)Also check that there are no name errors in the fields.
Tried with case
SELECT
_id,
CASE WHEN quotations IS NOT NULL AND quotations.status IS NOT NULL THEN quotations.status ELSE NULL END AS quotation_status,
CASE WHEN quotations IS NOT NULL AND quotations.totalAmount IS NOT NULL THEN quotations.totalAmount ELSE NULL END AS quotation_amount
FROM UNWIND(
leads WITH PATHS => (
quotations[OUTER => TRUE, INDEX => quotation_idx]
),
INDEX => idx,
OUTER => FALSE
)
Direct If Condition is not working so tried with CASE WHEN but also same error
CommandError { code: 96, code_name: "OperationFailed", message: "failed getting result set schema: algebrize error: Error 1007: Cannot access field `status` because it could not be found. below is my Schema
quotations
Array (1)
0
Object
status
"Yes"
totalAmount
10