Another Create Index request

[haworth76]

I am still struggling in creating indexes for array elements. In my example below, I wish to identify all elements with resourceType “Observation” and code “277208005” (using a select statement)

Any help would be greatly appreciated

A

[vsr1]

To create GSI index you need to know path in advance. Arbitrary path on arbitrary level make index creation impossible.

CREATE INDEX ix1 ON default( DISTINCT ARRAY
                                        (DISTINCT ARRAY
                                                  (DISTINCT ARRAY
                                                            c1.code
                                                            FOR c1 IN c.code.coding
                                                            END)
                                                  FOR c IN e.resource.contained
                                                  END)
                                         FOR e IN entry
                                         END)
WHER type = "searchset";

SELECT *
FROM default d
WHERE d.type = "searchset"
      AND ANY e IN d.entry
          SATISFIES (ANY c IN e.resource.contained
                     SATISFIES c.resourceType = "Observation" AND (ANY c1 IN c.code.coding
                                                                   SATISFIES c1.code = "xyz"
                                                                   END)
                     END)
          END;

That is more like FTS index use case.

[haworth76]

@vsr1

CREATE INDEX ix1 ON default( DISTINCT ARRAY  (DISTINCT ARRAY  (DISTINCT ARRAY c1.code FOR c1 IN c.code.coding END)FOR c IN e.resource.contained FOR e IN entry END) WHER type = "searchset";
SELECT *
FROM default d
WHERE d.type = "searchset" AND ANY e IN d.entry SATISFIES (ANY c IN e.resource.contained SATISFIES  c.resourceType = "bservation" AND (ANY c1 IN c.code.coding SATISFIES c1.code = "xyz" END) END) END;

Thanks for your quick response. When I run the query in Couchbase I obtain an error:

Any ideas?

A

[vsr1]

CREATE INDEX ix1 ON default( DISTINCT ARRAY
                                        (DISTINCT ARRAY
                                                  (DISTINCT ARRAY
                                                            c1.code
                                                            FOR c1 IN c.code.coding
                                                            END)
                                                  FOR c IN e.resource.contained
                                                  END)
                                         FOR e IN entry
                                         END)
WHER type = "searchset";

SELECT *
FROM default d
WHERE d.type = "searchset"
      AND ANY e IN d.entry
          SATISFIES (ANY c IN e.resource.contained
                     SATISFIES c.resourceType = "Observation" AND (ANY c1 IN c.code.coding
                                                                   SATISFIES c1.code = "xyz"
                                                                   END)
                     END)
          END;