如何使用PartiQL查询DynamoDB列表内指定id映射的目标字段值

问题描述 #

我有一个DynamoDB数据库，存储的数据结构如下所示：

{
  "userId": {
    "S": "someuserID"
  },
  "listOfMaps": {
    "L": [
      {
        "M": {
          "neededVal": {
            "S": "ThisIsNeeded1"
          },
          "id": {
            "S": "1"
          }
        }
      },
      {
        "M": {
          "neededVal": {
            "S": "ThisIsNeeded2"
          },
          "id": {
            "S": "2"
          }
        }
      }
    ]
  },
  "userName": {
    "S": "someuserName"
  }
}

字段listOfMaps是由映射类型元素构成的列表，列表长度不固定，可包含2个以上的映射元素。需求为编写PartiQL查询语句，实现根据userId以及列表内映射元素自身的id属性，获取对应元素的neededVal字段值。

目前已知可以通过指定列表下标的方式，查询第N个元素的neededVal值，查询语句示例如下：

SELECT "listOfMaps"[N]."neededVal" 
FROM "table-name" 
WHERE "userId" = 'someuserID'

想确认是否可以实现无下标匹配的查询逻辑，即直接通过匹配列表内元素的id属性完成过滤，获取对应的neededVal值，预期写法如下：

SELECT "listOfMaps"."neededVal" 
FROM "table-name"
WHERE "userId" = 'someuserID' AND "listOfMaps"."id" = '4'

解答 #

你设想的这种无下标直接穿透匹配列表内元素属性的写法无法直接生效，DynamoDB适配的PartiQL语法不支持对列表类型做跨元素的属性匹配，要拿到目标值可选择以下两种可行方案：

• 方案1：查询后业务层筛选（适合低频次查询场景）
  可以在WHERE条件中使用contains函数，先过滤出存在目标id元素的用户记录，拉取到完整的listOfMaps列表后，在业务代码中遍历列表找到对应id项的neededVal即可。
  对应查询语句：

  SELECT "listOfMaps"
  FROM "table-name"
  WHERE "userId" = 'someuserID' 
  AND contains("listOfMaps", {'id': '4'})
  

  注意：该语句会返回匹配用户的整条listOfMaps列表，DynamoDB不支持在查询阶段动态裁剪列表内容、仅返回匹配到的单个元素，必须在业务层做二次筛选。

• 方案2：调整数据模型（适合高频次查询场景）
  如果这类按id查列表内元素的操作是业务高频需求，建议直接调整表结构，将listOfMaps从列表类型改为映射类型，用每个元素的id作为映射的键，改造后的数据结构示例：

  {
    "userId": {"S": "someuserID"},
    "listOfMaps": {
      "M": {
        "1": {"M": {"neededVal": {"S": "ThisIsNeeded1"}}},
        "2": {"M": {"neededVal": {"S": "ThisIsNeeded2"}}},
        "4": {"M": {"neededVal": {"S": "ThisIsNeeded4"}}}
      }
    },
    "userName": {"S": "someuserName"}
  }
  

  改造后可以直接通过键路径精准定位到目标值，不需要遍历全量列表，查询性能更高，对应语句：

  SELECT "listOfMaps"."4"."neededVal"
  FROM "table-name"
  WHERE "userId" = 'someuserID'
  

补充说明：不要尝试使用[*]这类数组通配符编写查询路径，DynamoDB的PartiQL实现没有支持该语法，执行时会直接抛出语法错误。

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