How to improve query performance

SQL++
1

These are sample documents:

Userlink doc:
{
“_type”: “Userlink”,
“_cTime”: 1664312032,
“UserRecIDRefs”: [
“UserRec@@c750138f22bb54eab9e4835c93f34c5b”,
“UserRec@@e10a9af14b185c969815ab3aa456e853”,
“UserRec@@9e5dd18a539e45969651280f0419984d”,
“UserRec@@54ed47fdbbdf562eb1467f520fb7c00c”,
“UserRec@@8382b072ac174821ba00e7dfbf037e20”,
“UserRec@@46798ece616c4fd8b9b5a8f589951c38”
]
}

UserRec doc:
{
“_type”: “UserRec”,
“_inactiveTime”: 0,
“names”: [
{
“use”: 3001,
“family”: “Doe”,
“given”: “John”,
“prefix”: null,
“suffix”: null
}
],
“_cTime”: 1664312032,
“_mTime”: null
}

Search based on user names of UserRec, and the meta().id of UserRec must be in UserRecIDRefs array of Userlink doc. My query below is working. however I am not sure if we can improve the performance.

select raw UserRecIDRefs
from default c
where c._type = “Userlink”
and any v in c.UserRecIDRefs SATISFIES v in (SELECT raw meta(t).id FROM default t
WHERE t._type=‘UserRec’ AND (ANY pName in t.names SATISFIES LOWER(pName.family) like ‘doe%’ and LOWER(pName.given) like ‘jo%’ END) AND t._inactiveTime=0 ) end)

Thanks.

2

CREATE INDEX ix1 ON default (DISTINCT UserRecIDRefs) WHERE _type = "Userlink";

7.1+
CREATE INDEX ix71 ON default (DISTINCT ARRAY FLATTEN_KEYS(n.given, n.family) FOR n IN names END) WHERE _type = "UserRec" AND _inactiveTime = 0;

Pre 7.1

CREATE INDEX ix2 ON default (DISTINCT ARRAY n.given FOR n IN names END) WHERE _type = "UserRec" AND _inactiveTime = 0;

WITH userRecs AS (SELECT RAW meta(u).id
                  FROM default AS u
                  WHERE u._type = "UserRec"
                        AND ANY n IN u.names SATISFIES LOWER(n.family) LIKE "doe%" AND LOWER(n.given) LIKE "joe%" END
                        u._inactiveTime = 0)
SELECT RAW ul.UserRecIDRefs
FROM default AS ul
WHERE ul._type = "Userlink"
      AND ANY v IN ul.UserRecIDRefs SATISFIES v IN userRecs END
3

Thanks for the quick response.

I also need to return user names from userRecs type document for all IDs from UserRecIDrefs.
This is my query:

WITH userIDs AS (
SELECT RAW ul.UserRecIDRefs
FROM default AS ul
WHERE ul._type = “Userlink”
AND ANY v IN ul.UserRecIDRefs SATISFIES v IN (SELECT RAW meta(u).id
FROM default AS u
WHERE u._type = “UserRec”
AND ANY n IN u.names SATISFIES LOWER(n.family) LIKE “doe%” AND LOWER(n.given) LIKE “joe%” END
u._inactiveTime = 0) END )
select p.names,meta(p).id from default p
use keys (array v for v within {userIDs} end)

Is there a better query?

Thanks

4 
WITH userRecs AS (SELECT RAW meta(u).id
                  FROM default AS u
                  WHERE u._type = "UserRec"
                        AND ANY n IN u.names SATISFIES LOWER(n.family) LIKE "doe%" AND LOWER(n.given) LIKE "joe%" END
                        u._inactiveTime = 0)
SELECT DISTINCT u1.names, META(u1).id
FROM default AS ul
JOIN default AS u1 ON KEYS ul.UserRecIDRefs
WHERE ul._type = "Userlink"
      AND ANY v IN ul.UserRecIDRefs SATISFIES v IN userRecs END
5

I also have a class document like:
{
“_type”: “Class”,
“userRecDocIDRef”: “UserRec@@c750138f22bb54eab9e4835c93f34c5b”,
“classID”: “1.2.840.113711”,
“classCode”: “CS101”,
“_cTime”: 1664315032,
}

index: create index idx_class_userref on default (userRecDocIDRef) where _type = “Class”

I am trying to write a query to return both user and class info by join on userRecDocIDRef = META(u1).id from your above query, but I get “Cannot mix non ANSI JOIN with ANSI JOIN” error.

How to work around this error?

Thanks again!

6 
WITH UserDocs AS (
                  WITH userRecs AS (SELECT RAW meta(u).id
                  FROM default AS u
                  WHERE u._type = "UserRec"
                        AND ANY n IN u.names SATISFIES LOWER(n.family) LIKE "doe%" AND LOWER(n.given) LIKE "joe%" END
                        u._inactiveTime = 0)
                  SELECT DISTINCT RAW ulref
                  FROM default AS ul
                  UNNEST ul.UserRecIDRefs AS ulref
                  WHERE ul._type = "Userlink"
                        ANY v IN ul.UserRecIDRefs SATISFIES v IN userRecs END)
SELECT u1.names, META(u1).id, uc.*
FROM default AS u1 USE KEYS UserDocs
JOIN default AS uc ON uc.userRecDocIDRef = META(u1).id AND uc._type = "Class";
7

I am getting this error message:

{
“code”: 3000,
“msg”: “Ambiguous reference to field userRecs.”
}
WITH userRecs AS (SELECT RAW meta(u).id
FROM default AS u
WHERE u._type = “UserRec”
AND ANY n IN u.names SATISFIES LOWER(n.family) LIKE “doe%” AND LOWER(n.given) LIKE “joe%” END
u._inactiveTime = 0)
SELECT RAW ul.UserRecIDRefs
FROM default AS ul
WHERE ul._type = “Userlink”
AND ANY v IN ul.UserRecIDRefs SATISFIES v IN userRecs END
union
select RAW userRecs;

How can I fix this “msg”: “Ambiguous reference to field userRecs.”?

Thank you very much!

8

You are hitting MB-52412

Options: Use 7.1.2+
OR
repeat WITH in every ARM of UNION (set queries)

11

Is there other workaround?

The query needs to return all ids from userRecs type doc + matched ids from Userlink type doc.

Thanks.

12

Workaround already mentioned above

(WITH userRecs AS (SELECT RAW meta(u).id
                  FROM default AS u
                  WHERE u._type = "UserRec"
                  AND ANY n IN u.names SATISFIES LOWER(n.family) LIKE "doe%" AND LOWER(n.given) LIKE "joe%" END
                  u._inactiveTime = 0)
SELECT RAW ul.UserRecIDRefs
FROM default AS ul
WHERE ul._type = "Userlink"
AND ANY v IN ul.UserRecIDRefs SATISFIES v IN userRecs END)

UNION

(WITH userRecs AS (SELECT RAW meta(u).id
                  FROM default AS u
                  WHERE u._type = "UserRec"
                  AND ANY n IN u.names SATISFIES LOWER(n.family) LIKE "doe%" AND LOWER(n.given) LIKE "joe%" END
                  u._inactiveTime = 0)
SELECT RAW userRecs);
13

Is it possible to use outer join to get the same result?

Thanks.

14

Output representation is each UserRec, it list userLink

SELECT ul.UserRecIDRefs, d.id
FROM (SELECT RAW meta(u).id
      FROM default AS u
      WHERE u._type = "UserRec"
            AND ANY n IN u.names SATISFIES LOWER(n.family) LIKE "doe%" AND LOWER(n.given) LIKE "joe%" END
            AND u._inactiveTime = 0) AS d
LEFT JOIN default AS ul ON ul._type = "Userlink" AND ANY v IN ul.UserRecIDRefs SATISFIES v = d.id END;