Query based on fields that are UUIDs

SQL++
,
1

I have a document where some of the field names are uuids.

My query
SELECT *
FROM test AS d
WHERE d.orders.[0f17a3ac-addb-49e5-a687-b6124dfd371d].destination = “AAA”;

fails with incorrect syntax. What is the correct syntax for queries that include UUIDs ?

{
  "firstname": "m",
  "lastname": "bm",
  "orders": {
    "0f17a3ac-addb-49e5-a687-b6124dfd371d": {
      "destination": "AAA",
      "_created": "2020-01-09T16:50:41+00:00",
      "items": [
        "ink",
        "paper",
        "printer-paper"
      ]
    },
    "1f17a3ac-addb-49e5-a687-b6124dfd371d": {
    "destination": "AAA",
      "_created": "2020-01-15T16:50:41+00:00",
      "items": [
        "pencils",
        "paper",
        "markers"
      ]
    }
  },
  "phone": {
    "home": {
      "areacode": "999",
      "number": "9990000"
    },
    "mobile": {
      "areacode": "111",
      "number": "2223333"
    }
  }
}
2

Hi @famer1790,

The correct syntax to use is to backtick the field, so you would use:

WHERE d.orders.`0f17a3ac-addb-49e5-a687-b6124dfd371d`.destination = “AAA”;

I hope this helps you! :slight_smile:

Tim

3

In addition to @tim.bradgate, As you are using map syntax this may be helpfull

SELECT *
FROM test AS d
WHERE d.orders.["0f17a3ac-addb-49e5-a687-b6124dfd371d"].destination = "AAA"

 If you need to access the dynamic field you must use array brackets immediately 
 after dot (map access)   i.e  orders.[v] and v must be string or if it expression it must 
evaluate to string. It evaluates expression inside array brackets, it must be string, 
then converts into identifier and substitutes it and evaluate rest of the path.
 ex: orders.["xyz"] ==>  orders.`xyz` , orders.[f1] (f1 is "field1") ===> orders.`field1`