Simple SQL select not working on DocumentDB

Question

I have this very simple test document stored in DocumentDb:

{
  "DocumentDbTest_Countries": {
      "@country": "C24105130563",
      "@language": "C24105130563",
      "@countryCode": 1663405662,
      "@version": "2015-08-24T08:51:32:030.6165"
  },
  "id": "729607bd-38c5-2a96-0bc4-371a0a5bca4a"
}

Which I can find easily using the Document Explorer, and, also using the following Sql query in the Query Explorer:

SELECT * FROM DocumentDbTest_Countries c

But surprisingly when i run the following SQL query, it can't find the result:

SELECT * FROM DocumentDbTest_Countries c where c.country = 'C24105130563'

Can someone please guide me as to what I am doing wrong in the above SQL query?? I tried using double quotes instead of single but to no avail.

Aravind Krishna R. · Accepted Answer · 2015-08-25 19:40:28Z

1

Since @ is a special character, you have to escape it with the Dictionary-like [] operator. For example, run the query as:

SELECT * FROM DocumentDbTest_Countries c where c["@country"] = 'C24105130563'

answered Aug 25, 2015 at 19:40

Aravind Krishna R.

8,00332 silver badges39 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Deb · Accepted Answer · 2015-08-24 09:55:38Z

0

Found it. Its because of the @ sign present in the attributes.

Strangely I am using DocumentDB SDK to insert the data and those @ signs in the attributes are inserted by it. :(

Now have to find a way to remove the @ sign from the inserts.

answered Aug 24, 2015 at 9:55

Deb

7671 gold badge7 silver badges22 bronze badges

3 Comments

Aravind Krishna R. Over a year ago

Can you please post the code sample with the DocumentDB SDK?

Deb Over a year ago

Sure @AravindRamachandran I was trying to convert the following xml to JSON and save it to DocumentDB: <DocumentDbTest.Countries country="C25103657983" language="C25103657983" countryCode="383388823" version="2015-08-25T08:36:59:982.3552" /> Now the Newtonsoft.Json.JsonConvert.SerializeXmlNode adds the @ to the attributes by default, which gets saved as it to the DocumentDB without any issues/warnings. But then you can't search on it afterwards. :(

Aravind Krishna R. Over a year ago

Got it, please see how to escape the @ character in queries below.

Collectives™ on Stack Overflow

Simple SQL select not working on DocumentDB

2 Answers 2

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related