如何在Couchbase无N1QL查询结果时获取字段名（列名）

首先，我注意到你的查询语句里有中文单引号的问题（比如‘Phone Number’、‘customer’），N1QL只识别英文单引号，这可能是导致查询返回空数组的直接原因之一，先把这些中文单引号替换成英文单引号，确保语法正确。

接下来，针对“无匹配数据时返回带空值的表头”这个需求，我们可以通过UNION ALL结合IFNOTEXISTS子查询来实现，具体方案如下：

方案一：基础版（处理完全无匹配的情况） #

这个方案会在主查询没有返回任何结果时，自动返回一行所有字段为NULL的记录，保留你需要的表头：

SELECT 
  C.firstName AS `First Name`,
  C.surName AS `Last Name`,
  U.auditDetail.createTime AS `Date/Time of the Registration`,
  C.contactDetails.`email`.`value` AS `Email Address`,
  C.contactDetails.`phone`.`value` AS 'Phone Number',
  C.sex AS `Gender`,
  C.dob AS `Date of Birth`,
  ag.id AS `Referral Code`,
  C.addressDetails.`office_new`.`zipcode` AS 'Postal Code',
  C.addressDetails.`office_new`.`city` AS `City`
FROM data C
UNNEST C.activeGroups AS ag
JOIN data_PH U ON U.loginId = C.contactDetails.`email`.`value`
WHERE U.type_ = 'user' AND C.type_ = 'customer'

UNION ALL

-- 当主查询无结果时，返回一行空值记录
SELECT
  NULL AS `First Name`,
  NULL AS `Last Name`,
  NULL AS `Date/Time of the Registration`,
  NULL AS `Email Address`,
  NULL AS 'Phone Number',
  NULL AS `Gender`,
  NULL AS `Date of Birth`,
  NULL AS `Referral Code`,
  NULL AS 'Postal Code',
  NULL AS `City`
WHERE IFNOTEXISTS(
  -- 这里重复主查询的条件，判断是否有匹配数据
  SELECT 1 FROM data C
  UNNEST C.activeGroups AS ag
  JOIN data_PH U ON U.loginId = C.contactDetails.`email`.`value`
  WHERE U.type_ = 'user' AND C.type_ = 'customer'
)

方案二：进阶版（处理部分匹配的情况） #

如果你的主表data中存在type_='customer'的文档，但activeGroups为空或者data_PH中没有对应的用户记录，你可以用LEFT UNNEST和LEFT JOIN替代原来的UNNEST和JOIN，确保这些文档仍会被返回（对应字段为NULL），再结合UNION ALL处理完全无数据的情况：

SELECT 
  C.firstName AS `First Name`,
  C.surName AS `Last Name`,
  U.auditDetail.createTime AS `Date/Time of the Registration`,
  C.contactDetails.`email`.`value` AS `Email Address`,
  C.contactDetails.`phone`.`value` AS 'Phone Number',
  C.sex AS `Gender`,
  C.dob AS `Date of Birth`,
  ag.id AS `Referral Code`,
  C.addressDetails.`office_new`.`zipcode` AS 'Postal Code',
  C.addressDetails.`office_new`.`city` AS `City`
FROM data C
LEFT UNNEST C.activeGroups AS ag  -- 即使activeGroups为空，仍保留主文档
LEFT JOIN data_PH U 
  ON U.loginId = C.contactDetails.`email`.`value` 
  AND U.type_ = 'user'  -- 将U的类型条件移到JOIN中，避免过滤主文档
WHERE C.type_ = 'customer'

UNION ALL

-- 当主表中连customer类型的文档都没有时，返回空值记录
SELECT
  NULL AS `First Name`,
  NULL AS `Last Name`,
  NULL AS `Date/Time of the Registration`,
  NULL AS `Email Address`,
  NULL AS 'Phone Number',
  NULL AS `Gender`,
  NULL AS `Date of Birth`,
  NULL AS `Referral Code`,
  NULL AS 'Postal Code',
  NULL AS `City`
WHERE IFNOTEXISTS(
  SELECT 1 FROM data C WHERE C.type_ = 'customer'
)

关键说明 #

1. 语法修复：一定要把所有中文单引号替换成英文单引号，否则N1QL会把这些符号当作字符串的一部分，导致条件匹配失败。
2. IFNOTEXISTS的作用：它会检查子查询是否返回结果，如果没有，就执行后面的SELECT语句，返回空值行。
3. LEFT JOIN/LEFT UNNEST的作用：确保即使关联的子文档或其他桶没有匹配数据，主文档的记录仍会被保留，对应字段填充为NULL。

内容的提问来源于stack exchange，提问作者KARTHIKEYAN.A