Assistance with indexes for document

SQL++
,
1

Hi All. I have a document schema, such as this below. I would like to be able to find documents by domain name, and currently i have a query which works, but is not quick. I believe I also would likely need an index, and would appreciate if someone can provide an index and query for the below schema.

Thank you!

SELECT policy.* 
  FROM mybucket AS policy
UNNEST policy.environments AS envs
UNNEST envs.servers AS servers 
 WHERE policy.type = 'policy'
   AND servers.domainname = $1 

{
  policyId:
  environments: [
    {
      environmentType: 'test'
      servers: [
        {
          domain: "app.test.domain.com",
          ip: '10.10.10.10',
          port: 443,
        },
        {
          domain: "app.loadbalancer1.test.domain.com",
          ip: '10.10.10.10',
          port: 443,
        }
        {
          domain: "app.loadbalancer2.test.domain.com",
          ip: '10.10.10.10',
          port: 443,
        }
      ],
    },
    {
      environmentType: 'qa'
      servers: [
        {
          domain: "app.qa.domain.com",
          ip: '10.10.10.10',
          port: 443,
        },
        {
          domain: "app.loadbalancer1.qa.domain.com",
          ip: '10.10.10.10',
          port: 443,
        }
        {
          domain: "app.loadbalancer2.qa.domain.com",
          ip: '10.10.10.10',
          port: 443,
        }
      ],
    },
    {
      environmentType: 'production'
      servers: [
        {
          domain: "app.domain.com",
          ip: '10.10.10.10',
          port: 443,
        },
        {
          domain: "app.loadbalancer1.domain.com",
          ip: '10.10.10.10',
          port: 443,
        }
        {
          domain: "app.loadbalancer2.domain.com",
          ip: '10.10.10.10',
          port: 443,
        }
      ],
    }
  ]
}```
2

Query projects whole document avoid UNNEST instead use ANY with the following index query pair.

CREATE INDEX ix1 ON mybucket(DISTINCT ARRAY (DISTINCT servers.domainname
                                             FOR servers IN envs.servers
                                             END)
                                      FOR envs IN environments
                                      END)
WHERE type = "policy";

SELECT policy.*
FROM mybucket AS policy
WHERE policy.type = "policy"
      AND (ANY envs IN policy.environments
           SATISFIES (ANY servers IN envs.servers
                      SATISFIES servers.domainname = $1
                      END)
           END);
1 Like
3

thank you for quick assistance, marked it as solution. I found similar code elsewhere in the app, wondering if there was any performance difference between the 2 queries below?

SELECT policy.*
  FROM mybucket AS policy
 WHERE policy.type = "policy"
   AND (ANY envs IN policy.environments SATISFIES 
          (ANY servers IN envs.servers SATISFIES servers.domainname = $1 END)
        END);

SELECT policy.*
  FROM mybucket AS policy
 WHERE policy.type = "policy"
   AND (ANY s WITHIN environments[*].servers SATISFIES s.domainname = $1 END);
4

WITHIN is recursive (when you don’t know structure but looking some where in the document). Also may not use index. Use one suggested

1 Like
5

Thank you once again