Couchbase中N1QL查询获取Location列表及索引创建咨询

First, let's address the index creation. Since your document is a top-level JSON array, we need an array index that targets individual elements within the array, specifically the fields relevant to Location objects. This index will make your query efficient by covering all necessary fields, avoiding full document fetches.

Required Index #

Create this array index to support your Locale-based queries and ensure we can validate Location objects:

CREATE INDEX idx_location_fields ON FooBar (elem.Locale, elem.Venue, elem.Country)
FOR elem IN doc;

• FOR elem IN doc tells Couchbase to index every element in the document's top-level array.
• We index elem.Locale (our filter criteria) plus elem.Venue and elem.Country to create a covering index—this means the query can get all needed data directly from the index without hitting the bucket storage.

N1QL Query to Fetch Location Objects #

Use this query to retrieve only valid Location objects where Locale = 'QLD', excluding any other objects that might have a Locale field but lack the core Location attributes:

SELECT elem AS Location
FROM FooBar AS doc
UNNEST doc AS elem
WHERE elem.Locale = 'QLD'
  AND elem.Venue IS NOT NULL
  AND elem.Country IS NOT NULL;

Breakdown of the Query: #

• UNNEST doc AS elem: Converts the top-level array in each document into individual rows, one per array element.
• WHERE elem.Locale = 'QLD': Filters for elements matching your desired locale.
• AND elem.Venue IS NOT NULL AND elem.Country IS NOT NULL: Ensures we only return elements that have all the required attributes of a Location object, excluding any other objects that might happen to have a Locale field but aren't valid Locations.
• SELECT elem AS Location: Returns each valid element as a named Location object for clarity.

Why This Works for Your Scenario #

• Neutral Index: This index is specific to Location-related fields and won't conflict with existing indexes you've created. It only indexes array elements with the relevant attributes, keeping it focused.
• Exclusion of Non-Location Objects: By checking for the presence of Venue and Country, we ensure we don't accidentally return other objects that might include a Locale field but aren't part of your Location dataset.

内容的提问来源于stack exchange，提问作者The_Chud