Sub Array Indexing based on Primary Index

SQL++
1

Hi everyone,

I have my data organized in following manner:

KEY: id1 (This is primary index, meta().id)

{
  "id": "id1",
  "subdata": [
    {
      "subID": "subid1",
      "subDescription": "some data here"
    },
    {
      "subID": "subid2",
      "subDescription": "some data here"
    }
  ]
}

Similarly I have multiple documents. I am wondering how to create an index on subID element of array in the document, so that it will act as a secondary index. I have thousands of json objects under subdata field.
When I say secondary index, I meant that I will do a query with WHERE condition having both meta().id and subID.

CREATE INDEX doc_subID ON `doc-sample`
 (ALL DISTINCT ARRAY v.subID FOR v IN subdata END);

If I am not wrong, above will create index across all arrays in all documents of the collection. Whereas I would prefer to have it kind of secondary index, so first it will search based on primary index using id and for that document, it will search in the array using secondary index of subID.

2 
CREATE INDEX doc_subID ON `doc-sample`
 (id, ALL DISTINCT ARRAY v.subID FOR v IN subdata END);
SELECT *
FROM `doc-sample`
WHERE id = "id1" AND ANY v IN subdata SATISFIES v.subID = "subid1" END;

https://index-advisor.couchbase.com/

3

I am new to n1ql.
I have similar document structure and I want to replace all records which has “subDescription”: “ABC” with ““subDescription”: “DEF””.
Can you please help me in creating index and update query to do that?

4 
CREATE INDEX doc_subID ON `doc-sample`
 (ALL DISTINCT ARRAY v.subDescription FOR v IN subdata END);

UPDATE  `doc-sample`  AS d  SET sd.subDescription = "DEF"  FOR sd IN d.subdata WHEN sd.subDescription = "ABC" END
WHERE ANY v IN d.subdata SATISFIES v.subDescription = "ABC" END;

https://docs.couchbase.com/server/current/n1ql/n1ql-language-reference/update.html