Create secondary index on attribute inside dynamic object attribute

Couchbase Server
, ,
1

Hi,

I have a sample doc like:

 "reqProcs": {
    "12345": {
      "UID": "abcd123",
      "refPhy": {
        "id": {
          "use": 3001,
          "type": "STARTER1",
          "system": "STARTER1",
          "value": "468268321"
        },
        "name": {
          "use": 3001,
          "family": "lname1",
          "given": "fname1",
          "other": [],
          "prefix": "",
          "suffix": ""
        }
       } 
      }
    "78901": {
      "UID": "abcd456",
      "refPhy": {
        "id": {
          "use": 3001,
          "type": "STARTER2",
          "system": "STARTER2",
          "value": "0123456"
        },
        "name": {
          "use": 3001,
          "family": "lname2",
          "given": "fname2",
          "other": [],
          "prefix": "",
          "suffix": ""
        }
       } 
      }
    }

Is it possible to create a secondary index on reqProcs..refPhy.name? id is dynamic value.

Thanks,

Matt

2

@mhou

CREATE INDEX ix1 ON default( DISTINCT ARRAY FLATTEN_KEYS(r.val.refPhy.id.use, r.val.refPhy.id.`value`) FOR r IN OBJECT_PAIRS(reqProcs) END);

SELECT d.*
FROM default AS d
WHERE ANY r IN OBJECT_PAIRS(reqProcs) SATISFIES r.val.refPhy.id.use = 3001 AND r.val.refPhy.id.`value` = "468268321" END;
3

Hi vsr1,

Thank you for your response.

I actually want to do case insensitive search on refPhy.name.family and given. Can you advice how to build an index?

Thanks!