1

When I execute query

SELECT Group FROM DIGITALTWINS Sensor JOIN Group RELATED Sensor.contains WHERE Sensor.$dtId='xxx’

I'm getting error

RestError: SQL query parse failed: SQL Parser Error, Line=1, Position=7, Message=mismatched input 'Group' expecting {AVG, CONTAINS, COUNT, DEVICES_JOBS, DEVICES_MODULES, DEVICES, DIGITALTWINS, RELATIONSHIPS, JOIN, MAX, MIN, RELATED, SUM, TOP, EXACT, DEFAULT, '*', IDENTIFIER}
SQL Parser Error, Line=1, Position=13, Message=mismatched input 'FROM' expecting BY See samples in http://aka.ms/adtv2query for the correct syntax.

But when I change “Group” to another word, ex “a” Query succeeded. I'm getting this same error when I use the word “Order”. I think this is because this word is used in normal SQL queries, and even if digital twins don't support it, still want to parse it and I got an error.

Where I can find a list of the forbidden words for queries?

Kajoj
  • 13
  • 2

2 Answers2

3

I see that you have mixed single quotes: ' vs ’ (I am not sure how they are named). Sometimes it happens when copying and pasting text.

Update: Group word (as well as any other reserved word) has to be escaped using square brackets: [[<IDENTIFIER>]].

Query:

SELECT [[Group]] FROM DIGITALTWINS Sensor JOIN [[Group]] RELATED Sensor.contains WHERE Sensor.$dtId='xxx'
Petr
  • 349
  • 1
  • 4
  • I have used '' quotes in the query and I can still reproduce the issue when using the word "Group" – asergaz Aug 27 '21 at 08:20
  • 1
    @asergaz you are right, quotes were not the only issue, updated the answer – Petr Aug 27 '21 at 19:35
  • @Petr thanks, escaping words worked for me! I also corrected quotes (I added it when I was posting the query, it had no impact on original query) Do you have a list of forbidden words? – Kajoj Aug 30 '21 at 07:39
2

Here is the set of "reserved" keywords in the language:

  • ALL
  • AND
  • AS
  • ASC
  • AVG
  • BY
  • COUNT
  • DESC
  • DEVICES_JOBS
  • DEVICES_MODULES
  • DEVICES
  • ENDS_WITH
  • FALSE
  • FROM
  • GROUP
  • IN
  • IS_BOOL
  • IS_DEFINED
  • IS_NULL
  • IS_NUMBER
  • IS_OBJECT
  • IS_PRIMITIVE
  • IS_STRING
  • MAX
  • MIN
  • NOT
  • NOT_IN
  • NULL
  • OR
  • ORDER
  • SELECT
  • STARTS_WITH
  • SUM
  • TOP
  • TRUE
  • WHERE
  • IS_OF_MODEL

These words cannot be used as identifiers without enslosing them with [[ ]].

[UPDATE] See more details in the documentation: https://learn.microsoft.com/en-us/azure/digital-twins/reference-query-reserved

asergaz
  • 996
  • 5
  • 17