CosmosDB [ Document DB] SQL

Question

I am new to Cosmos db [ Document db] . I am trying to write and SQL to fetch data from the collection .

my collection looks like

//
[
 {
"id": "SitefinitySocMapping",
"DocumentCreationTime": "2017-10-10T14:08:12.3916921Z",
"TotalRecords": 95,
"Value": [
  {
    "odata.type": null,
    "Id": "4ccae28d-53a7-474b-a685-39d0a7eda7ea",
    "LastModified": "2017-09-12T15:04:35Z",
    "PublicationDate": "2017-09-08T12:49:19Z",
    "ExpirationDate": null,
    "DateCreated": "2017-09-08T12:49:19Z",
    "UrlName": "3532",
    "Description": "Brokers",
    "SOCCode": "3532",
    "NavigateToApprenticeshipStandard": [],
    "NavigateToApprenticeshipFramework": [
      {
        "UrlName": "455"
      }
    ]
  },
  {
    "odata.type": null,
    "Id": "ab39804a-0d72-45bc-bec4-1d78a0894bf4",
    "LastModified": "2017-09-12T13:31:15Z",
    "PublicationDate": "2017-09-08T12:43:29Z",
    "ExpirationDate": null,
    "DateCreated": "2017-09-08T12:43:29Z",
    "UrlName": "2413",
    "Description": "Solicitors",
    "SOCCode": "2413",
    "NavigateToApprenticeshipStandard": [],
    "NavigateToApprenticeshipFramework": [
      {
        "UrlName": "565"
      }
    ]
  },
  {
    "odata.type": null,
    "Id": "3c26852d-7006-43a0-bc4d-23b5eb6c4772",
    "LastModified": "2017-09-12T13:31:51Z",
    "PublicationDate": "2017-09-08T12:44:09Z",
    "ExpirationDate": null,
    "DateCreated": "2017-09-08T12:44:09Z",
    "UrlName": "2426",
    "Description": "Business and related research professionals",
    "SOCCode": "2426",
    "NavigateToApprenticeshipStandard": [],
    "NavigateToApprenticeshipFramework": [
      {
        "UrlName": "410"
      }
    ]
  },
  {
    "odata.type": null,
    "Id": "1a685a7b-c03c-4477-8d71-244bef48f69d",
    "LastModified": "2017-09-12T15:04:52Z",
    "PublicationDate": "2017-09-08T12:49:27Z",
    "ExpirationDate": null,
    "DateCreated": "2017-09-08T12:49:27Z",
    "UrlName": "3534",
    "Description": "Finance and investment analysts and advisers",
    "SOCCode": "3534",
    "NavigateToApprenticeshipStandard": [],
    "NavigateToApprenticeshipFramework": [
      {
        "UrlName": "455"
      }
    ]
  }
],
"_rid": "6oxhAPO5fwABAAAAAAAAAA==",
"_self": "dbs/6oxhAA==/colls/6oxhAPO5fwA=/docs/6oxhAPO5fwABAAAAAAAAAA==/",
  "_etag": "\"0100c389-0000-0000-0000-59dcd44a0000\"",
"_attachments": "attachments/",
   "_ts": 1507644490
 }
 ]

//Now I am trying to run the query against it . A simple query to check the "WHERE" and the "JOIN" Clause.

My Query is

 SELECT * from vacancy c join v in c.Value where v.SOCCode = "3532"

after executing this query its giving an error

 Syntax error, incorrect syntax near 'Value'.

Please do advise how can i write a SQL query for the nested element as well as what is wrong in my query

score 0 · Answer 1 · answered Oct 10 '17 at 15:33

I got the answer. As the Value is the reserved keyword so we need to be careful while selecting / Creating the Json Object which might contains the reserved keyword.

In this situation we can handle our query by using "" or if using join we can use ["]

SELECT v.UrlName,v.Description from vacancy f Join v in f["Value"] where v.SOCCode = "3532"

CosmosDB [ Document DB] SQL

Please do advise how can i write a SQL query for the nested element as well as what is wrong in my query

1 Answers1