[SOLVED] Select a certain map element in a list using PartiQL (DynamoDB)

Select a certain map element in a list using PartiQL (DynamoDB)

Although I understood how to select a list item in a map using document paths (eg Devices.FireStick.DateWatched[0]),I cannot figure out how to do it the other way round. Say, I have an attribute containing a list of maps:

"Text": [
  {
   "Right": "Line one, right text",
   "Left": "Line one, left text"
  },
  {
   "Right": "Line two, right text",
   "Left": "Line two, left text"
  }]

and I want to get the left text of line one in my PartiQL select. My naive approach:

select Text[0].Right  from mytable where ID='123'

did not result in success.

Solution

You need to double-quote DynamoDB reserved words, such as "right".

Here's a working PartiQL query:

SELECT Text[0]."Right" FROM mytable WHERE id = '1'

Also, note that if your table name includes a hyphen then quote that too, for example:

SELECT Text[0]."Right" FROM "mytable-xyz" WHERE id = '1'