How to get Main Doc and optional subdoc based on filter

SQL++
1

I have the below query which gets me the login info for user as well as the default email and phone number. All works fine if the user has an email and phone as well as it is set to dflt.
If the user does not have both and and they are not set to dflt it will not return a record.
So how could i get always the record and filter only if there is a phone and email and its set to dflt.

Better would be get the email if there is only one, if there is more then 1 get the one which is dflt and same for the phone. Is that posible ?

SELECT META(c).id,
                    c._id AS user_guid,
                    c.username,
                    c.security.failed_login_count,
                    c.security.locked,
                    c.security.`password`,
                    c.security.`password_expiration`,
                    c.security.admin,
                    c.security.developer,
                    c.security.rsa,
                    c.security.password_salt,
                    c.security.login_history,
                    c.name.fname AS f_name,
                    c.name.mname AS m_name,
                    c.name.lname AS l_name,
                    e.address AS email,
                    p.`number` AS phone
                FROM Contacts c
                    UNNEST c.emails e
                    UNNEST c.phones p
                WHERE c._type = 'user'
                    AND c.username = $1
                    AND e.dflt = TRUE
                    AND p.dflt = TRUE
2

Hi @aponnath ,

Don’t do UNNEST use

FIRST e.address FOR e IN c.emails WHEN e.dflt OR ARRAY_LENGTH(c.emails) = 1 END

Try following

SELECT META(c).id,
    c._id AS user_guid,
    c.username,
    c.security.failed_login_count,
    c.security.locked,
    c.security.`password`,
    c.security.`password_expiration`,
    c.security.admin,
    c.security.developer,
    c.security.rsa,
    c.security.password_salt,
    c.security.login_history,
    c.name.fname AS f_name,
    c.name.mname AS m_name,
    c.name.lname AS l_name,
    FIRST e.address FOR e IN c.emails WHEN e.dflt  OR ARRAY_LENGTH(c.emails) = 1 END AS email,
    FIRST p.`number` FOR p IN c.phones WHEN p.dflt OR ARRAY_LENGTH(c.phones) = 1 END AS phone
FROM Contacts c
WHERE c._type = 'user' AND c.username = $1
3

Thanks that does the trick as always