CosmosDB [文档数据库] SQL

Question

我是第一次接触Cosmos db文档db。我正在尝试编写和SQL来从集合中获取数据。

我的收藏看起来像

//
[
 {
"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
 }
 ]

//现在我正在尝试对其运行查询。检查"WHERE“和"JOIN”子句的简单查询。

我的问题是

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

执行此查询后，会给出一个错误

 Syntax error, incorrect syntax near 'Value'.

请告诉我如何为嵌套元素编写SQL查询，以及查询中的错误之处

Answer 1

我知道答案了。由于值是保留关键字，因此在选择/创建可能包含保留关键字的Json对象时需要小心。

在这种情况下，我们可以使用"“来处理查询，如果使用join，则可以使用”

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