DynamoDB how to use index in PartiQL queries?

Question

I have seen the dynamoDB doc for the PartiQL syntax:

SELECT expression  [, ...] 
FROM table[.index]
[ WHERE condition ] [ [ORDER BY key  [DESC|ASC] , ...]

but in practice:

select * from dev .pk-all-index
where "pk" = 'config' AND ("brand" = 'tesla' OR contains("aliases", 'tesla.com'))

gives me the error:

An error occurred during the execution of the command. ValidationException: Statement wasn't well formed, can't be processed: Unexpected keyword

Did you mean that you need to use `"dev"."pk-all-index"`? That might be the result of the `-` in the identifier, and maybe your schema is case-sensitive. — Mark Rotteveel, Feb 11 '21 at 16:29

score 14 · Accepted Answer · edited Feb 14 '21 at 21:51

14

from "tablename"."indexname"

edited Feb 14 '21 at 21:51

Dharman

answered Feb 09 '21 at 17:49

tang chao

4

Please add some explanation to your answer such that others can learn from it – Nico Haase Feb 10 '21 at 10:20
2

hmmm i feel like this is straightforward enough? – tang chao Feb 16 '21 at 04:49
1

Is it? What does your answer tell the OP? It does not contain a valid SQL statement, it does not contain the table names used in his own query, it does not tell him what he did wrong – Nico Haase Feb 16 '21 at 07:03
This syntax is documented here https://docs.aws.amazon.com/amazondynamodb/latest/developerguide/SQLtoNoSQL.Indexes.QueryAndScan.html – Luke Jan 29 '23 at 10:05

score 6 · Answer 2 · answered Apr 19 '21 at 18:05

6

You might want to put the table name and index under quotes, individually.

SELECT * FROM "dev"."pk-all-index" WHERE "pk" = 'config' AND ("brand" = 'tesla' OR contains("aliases", 'tesla.com'))

answered Apr 19 '21 at 18:05

2 Answers2