Query Array in JSON with Azure Synapse Serverless

Question

Query Array in JSON with Azure Synapse Serverless

Christopher Mühl 111

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?

Synapse-1

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

MartinJaffer-MSFT 26,161 Reputation points

2023-02-14T18:34:51.25+00:00

@Christopher Mühl Did my response help you? If it solved your problem, please mark as accepted answer. If you still need assistance on this, please let me know. If you found your own solution, please share here.

Answer accepted by question author

0 additional answers

Your answer

MartinJaffer-MSFT 26,161 Reputation points

2023-02-14T18:34:51.25+00:00

@Christopher Mühl Did my response help you? If it solved your problem, please mark as accepted answer. If you still need assistance on this, please let me know. If you found your own solution, please share here.

Answer 1

MartinJaffer-MSFT 26,161

@Christopher Mühl Hello and welcome to Microsoft Q&A.

I understand you are having trouble extracting simple values from an array vis Synapse Link for Cosmos DB for MOngoDB.

At first, looking at your samples, everything seemed find and promised to be a real headscratcher. You are accessing things correctly. So then I figured I should question my assumptions. Is it 0-based index or 1-based index? No I figured you would have caught that. So I look closer.

Coordinates is an array of floats/double/decimal. Not an array of strings.

Your query expects Varchar (string) types.

I'm thinking implicit conversion/typecasting is not happening. However, If that is the case, why didn't you have to convert Coordinates?

I checked in the link you provided, underneath "CosmosDB to SQL type mappings". I found

Nested object or array	varchar(max) (UTF-8 database collation), serialized as JSON text

This means it implicitly converted Coordinates for you, but does not implicitly convert referenced elements.

So I believe you need to explicitly convert like you did for id .

Christopher Mühl 111

Hello @MartinJaffer-MSFT ,

sorry for the late reply. there is a lot to do.

And yes I was able to solve the problems with your tips and further research.

The 1st query - direct access to an array element I solved as follows:

SELECT
		CONVERT(VARCHAR(50), [_id], 2) AS [Id]
		,CONVERT(FLOAT, JSON_VALUE([Coordinates], '$.coordinates[0]'),2) AS Longitude
		,CONVERT(FLOAT, JSON_VALUE([Coordinates], '$.coordinates[1]'),2) AS Latitude
FROM OPENROWSET(PROVIDER = 'CosmosDB',
                CONNECTION = 'Account=xyz;Database=abc',
                OBJECT = 'Object',
                CREDENTIAL = 'credential'
) 
WITH (
	[_id] VARBINARY(50)
	,[Coordinates] VARCHAR (100) '$.Location'
)
AS [Objects]

And the second query I solved as follows:

SELECT
		CONVERT(VARCHAR(50), [_id], 2) AS [ObjectId]
		,UPPER([ImageId]) AS [ImageId]
FROM OPENROWSET(
				PROVIDER = 'CosmosDB',
                CONNECTION = 'Account=xyz;Database=abc',
                OBJECT = 'Object',
                CREDENTIAL = 'credential'
				) 
	WITH (
		[_id] VARBINARY(50)
		,Images VARCHAR(MAX) '$.Images'
	)
AS [Objects]
CROSS APPLY OPENJSON([Images])
	WITH ([ImageId] VARCHAR(50) '$')

Best regards,

Christopher

MartinJaffer-MSFT 26,161 Reputation points

2023-02-17T20:26:16.98+00:00

Great! thanks for sharing that. Please mark as accepted answer so others can find solutions easier. (helps train models)

Share via

Query Array in JSON with Azure Synapse Serverless

0 additional answers

Your answer