How to select an object within a collection?

Thanks for the query.
But it is not working if more than one values are passed in standards like below.
SELECT b.lineItemID,
b.metadata,
b.language,
b.type,
OBJECT_ADD({},“note”,b.standards.note) AS standards
FROM default b
WHERE [“note1”,“notes2”,…] IN b.standards.note

Please suggest .

FYI

SELECT b.lineItemID,
       b.metadata,
       b.language,
       b.type,
       OBJECT_ADD({},"note",b.standards.note) AS standards
  FROM default b
 WHERE ANY n IN b.standards.note SATISFIES n IN ["note1","note2","note3"] END

Thanks a lot for you help. The query is working now.

Hi…
I have a new requirement for the above data like below:
{
“lineItemID”: 1,
“standards”: {
“other”: [
{id:“other1”,referenceUrl:""},
{id:“other2”,referenceUrl:""}
]
“note”: [
{id:“note1”,referenceUrl:""},
{id:“note2”,referenceUrl:""},
{id:“note3”,referenceUrl:""}
]
},
“language”: "Sample Language ",
“type”: “H”,
“metadata”: [ “metadata1”, “metadata2” ]
}

Now, need to select “note1”,“note3” from the “standards” in this json. I am using unnest clause to fetch.
Can you please help.

SELECT b.lineItemID,
       b.metadata,
       b.language,
       b.type,
       {"note": note} AS  standards
  FROM default b
 LET note = ARRAY v FOR v IN b.standards.note WHEN v.id IN  ["note1","note2"] END
 WHERE ARRAY_LENGTH(note) > 0;

It works. Thanks a lot… :slight_smile:

Hi…
I have two documents like below:
Document 1-
{
“eAstandards”: {
“asu201409”: [
{
“adoptableDate”: “12/01/2016”,
“entityType”: “PBE”,
“requiredDate”: “12/16/2017”
}
],
“asu201509”: [
{
“adoptableDate”: “12/01/2016”,
“entityType”: “PBE”,
“requiredDate”: “12/16/2017”
}
]
}
}

Document 2-
{
“lineItemID”: 5539,
“indent”: 2,
“earlyAdoptionTag”: “asu201509”,
“isDeleted”: false,
“smefinallanguage”: “Balance Sheet”,
“createdDate”: “10/12/2017”,
“createdBy”: “test user4”
}

Now, I want to display the data for lineItemID 5539 , like below where the details of earlyAdoptionTag will show like below:
O/P-
{
“lineItemID”: 5539,
“indent”: 2,
“earlyAdoptionTag”: “asu201509”,
“eAstandards”: {
[
{
“adoptableDate”: “12/01/2016”,
“entityType”: “PBE”,
“requiredDate”: “12/16/2017”
}
]
},
“isDeleted”: false,
“smefinallanguage”: “Balance Sheet”,
“createdDate”: “10/12/2017”,
“createdBy”: “test user4”
}

Please help me with the select query.

What are the Document Ids for Document 1 and Document 2?

The document ids are different for doc 1 & doc 2
suppose document 1 has id “1” and document 2 has id “2”

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

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

is it not correct?

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.

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`

Thanks a lot. Really appreciate your help.

It seems work,Thank your for your answer.

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.

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

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:

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.

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