Query simple Array (without Object inside) with Serverless from Cosmos DB For MongoDB

Question

Hello community,

I am currently trying to query arrays via Synapse Serverless. (Synapase Link to CosmosDB for MongoDB)

There is a lot of documentation on how to query objects inside arrays, but I am failing on the supposedly easier query without object.

My object looks like this:

{
	"_id" : ObjectId("abc123"),
	"Location" : {
		"type" : "Point",
		"coordinates" : [
			6.840024763111111,
			51.485867243777776
		]
	},
	"Images" : [
		"ImageFileName1",
		"ImageFileName2",
		"ImageFileName3",
		"ImageFileName4"
	]
}

In the first query I want to query the coordinates as single columns for longitude and latitude. There are always exactly 2 array elements (0 and 1).

As you can see in the screenshot (Synapse-1.png), I can query the array, but when I try to access the first element, I get zero back.

What am I doing wrong here?

In the second query I want to query the array "Images" on single rows.

Like this:

Id | Image

abc123 | ImageFileName1

abc123 | ImageFileName2

abc123 | ImageFileName3

abc123 | ImageFileName4

Of course I found the documentation (https://learn.microsoft.com/en-us/azure/synapse-analytics/sql/query-cosmos-db-analytical-store?tabs=openrowset-credential#flatten-nested-arrays) where objects inside an array are queried in the desired way, but without an object inside, how can I do this query?

Thank you in advance and best regards

Christopher

Answer 1

Hi Christopher Mühl,

Thank you for posting query in Microsoft Q&A Platform.

Regarding getting first item from array, I would suggest you use SUBSTRING() and CHARINDEX() functions on top of your array string. Please check below example.

below is the code for substring related function which I used in above screenshot.

SUBSTRING(coordinates,2,CHARINDEX(',',coordinates)-2)

Since your images array also has only values with keys, I would recommend to use STRING_SPLIT and REPLACE() functions on top of Images array string. Kindly check below screenshot.

Hope this helps. Please let me know if any further queries.

---

Please consider hitting Accept Answer button. Accepted answers help community as well.