How to select an object within a collection?

SQL++
21

To Join in the N1QL the relation between documents needs to be present through the document key.
The following link explain full details how to JOIN in the N1QL https://dzone.com/articles/visually-explaining-n1ql-joins

i.e. doc1 needs to contain the doc2 document id or vice versa to join

22

Document 1 has “asu201509” which is present in document 2 as “earlyAdoptionTag”: “asu201509”,

is it not correct?

23

Please check this out N1QL subquery in select
In N1QL the join relation ship needs through document key. N1QL doesn’t support join relation ship through different fields in the document.

24

If you don’t have join relation through document key ,

You can do independent queries and JOIN through ARRAY’s. This may not best option but that is only option.

    SELECT d.*,ARRAY v.val FOR v IN ud WHEN d.earlyAdoptionTag = v.name END AS eAstandards FROM default AS d USE KEYS ["doc2"]
    LET ud = ARRAY_FLATTEN((SELECT RAW OBJECT_PAIRS(d2.eAstandards) FROM default AS d2 USE KEYS ["doc1"]),2);

If  Couchbase Versions  is pre 4.6.0 change
 v.val to v.`value`
25

Thanks a lot. Really appreciate your help.

26

It seems work,Thank your for your answer.

27

Hi…
I am using query like below :

SELECT meta(ch).id as uid,
ch.approvedBy,
ch.approvedDate,
ch.createdBy,
ch.createdDate,
ch.earlyAdoptionDelete,
ch.earlyAdoptionFlag,
ch.earlyAdoptionTag,
ch.indent,
ch.isDeleted,
ch.lineItemID,
ch.metadata,
ch.publishDate,
ch.publishedBy,
ch.referenceJson,
ch.smefinallanguage,
ch.standardid,
ch.status,
ch.submittedBy,
ch.submittedDate,
ch.type,
ch.version,
ch.versionHistory,
ch.updateDescriptionId,
ARRAY i FOR i IN ud WHEN ch.updateDescriptionId = i.id END AS updateDescriptionDetails,
First v.val FOR v IN ud1 WHEN ch.earlyAdoptionTag = v.name END AS earlyAdoptionTagDetails
FROM ChecklistLibrary AS ch
LET
ud = ARRAY_FLATTEN((SELECT RAW chl.updateDescriptions FROM ChecklistLibrary chl USE KEYS [“For Profit:updateDescription”]),2)
,ud1 = ARRAY_FLATTEN((SELECT RAW OBJECT_PAIRS(chl1.eAstandards) FROM ChecklistLibrary AS chl1 USE KEYS [“For Profit:eAStandards”]),2)
,draftLineItemData= (SELECT chd.earlyAdoptionFlag,
chd.metadata,
chd.lineItemID,
chd.indent,
chd.publishDate,
chd.approvedBy,
chd.standardid,
chd.type,
chd.submittedDate,
chd.smefinallanguage,
chd.version,
chd.earlyAdoptionTag,
chd.updateDescriptionId,
chd.approvedDate,
chd.createdDate,
chd.isDeleted,
chd.position,
chd.status,
ARRAY i FOR i IN ud2 WHEN chd.updateDescriptionId = i.id END as updateDescriptionDetails
FROM ChecklistLibrary AS ch1 unnest ch1.draftLineItemData chd
LET
ud2 = ARRAY_FLATTEN((SELECT RAW chl.updateDescriptions FROM ChecklistLibrary chl
USE KEYS [“For Profit:updateDescription”]),2)
where meta(ch1).id like “For Profit:1”)
where meta(ch).id like “For Profit:1”

But it is giving the below error-
[
{
“code”: 5010,
“msg”: “Error evaluating LET. - cause: FROM in correlated subquery must have USE KEYS clause: FROM ChecklistLibrary.”
}
]

Could you tell where I need to add use keys clause.

28

Remove the LET and replace with actual LET expression and try it. Check this out Error evaluating filter. - cause: FROM in correlated subquery must have USE KEYS

29

Can you please share me the query .
I have checked the link and prepared the query like -

SELECT meta(ch).id as uid,
ch.approvedBy,
ch.approvedDate,
ch.createdBy,
ch.createdDate,
ch.earlyAdoptionDelete,
ch.earlyAdoptionFlag,
ch.earlyAdoptionTag,
ch.indent,
ch.isDeleted,
ch.lineItemID,
ch.metadata,
ch.publishDate,
ch.publishedBy,
ch.referenceJson,
ch.smefinallanguage,
ch.standardid,
ch.status,
ch.submittedBy,
ch.submittedDate,
ch.type,
ch.version,
ch.versionHistory,
ch.updateDescriptionId,
ARRAY i FOR i IN ud WHEN ch.updateDescriptionId = i.id END AS updateDescriptionDetails,
First v.val FOR v IN ud1 WHEN ch.earlyAdoptionTag = v.name END AS earlyAdoptionTagDetails
FROM ChecklistLibrary AS ch
LET
ud = ARRAY_FLATTEN((SELECT RAW chl.updateDescriptions FROM ChecklistLibrary chl USE KEYS [“For Profit:updateDescription”]),2)
,ud1 = ARRAY_FLATTEN((SELECT RAW OBJECT_PAIRS(chl1.eAstandards) FROM ChecklistLibrary AS chl1 USE KEYS [“For Profit:eAStandards”]),2)
,draftLineItemData= (SELECT chd.earlyAdoptionFlag,
chd.metadata,
chd.lineItemID,
chd.indent,
chd.publishDate,
chd.approvedBy,
chd.standardid,
chd.type,
chd.submittedDate,
chd.smefinallanguage,
chd.version,
chd.earlyAdoptionTag,
chd.updateDescriptionId,
chd.approvedDate,
chd.createdDate,
chd.isDeleted,
chd.position,
chd.status,
ARRAY i FOR i IN ud2 WHEN chd.updateDescriptionId = i.id END as updateDescriptionDetails
FROM ChecklistLibrary AS ch1 unnest ch1.draftLineItemData chd
where
ud2 == ARRAY_FLATTEN((SELECT RAW chl.updateDescriptions FROM ChecklistLibrary chl
USE KEYS [“For Profit:updateDescription”]),2)
and meta(ch1).id like “For Profit:1”)
where meta(ch).id like “For Profit:1”

but again error showing :
{
“code”: 3000,
“msg”: “Ambiguous reference to field ud2.”,

Please help :frowning:

30

In the latest post you have never defined ud2 that is why it gives error.

In your first query where did use draftLineItemData in outer LET.

31

Hii…
now able to build the query as per the requirement.
Thanks for your suggestion.