Querying Cosmos Nested JSON documents

Question

I would like to turn this resultset

[
  {
    "Document": {
      "JsonData": "{\"key\":\"value1\"}"
    }
  },
  {
    "Document": {
      "JsonData": "{\"key\":\"value2\"}"
    }
  }
]

into this

[
  {
    "key": "value1"
  },
  {
    "key": "value2"
  }
]

I can get close by using a query like

select value c.Document.JsonData from c

however, I end up with

[
  "{\"key\":\"value1\"}",
  "{\"key\":\"value2\"}"
]

How can I cast each value to an individual JSON fragment using the SQL API?

It would really help if you edited your question to show what your documents look like. That said: Cosmos DB has no JSON parsing built-in. You'd need to transform such data from within your app (or a stored procedure). Or store nested content as actual nested documents instead of JSON strings. — David Makogon

Lee Liu Lee Liu · Accepted Answer · 2018-06-20T06:50:14

As David Makogon said above, we need to transform such data within our app. We can do as below:

        string data = "[{\"key\":\"value1\"},{\"key\":\"value2\"}]";
        List<Object> t = JsonConvert.DeserializeObject<List<Object>>(data);
        string jsonData = JsonConvert.SerializeObject(t);

Screenshot of result:

Querying Cosmos Nested JSON documents

1 Answers