JSON text is not properly formatted. Unexpected character 'k' is found at position 1.

Pratisti Satardekar 130 Reputation points
2023-11-30T13:10:24.3233333+00:00

Hi,

When i run my query on MSSMS using CROSS APPLY OPENJSON it gives me the result. but when i try to get the data using the same query in ADF dataflow it throws me an error "JSON text is not properly formatted. Unexpected character 'k' is found at position 1."
This is how the data looks like [{"label":"New drop Option 1","value":"key-1","key":"key-1"}] (DataType is text).

This is how im querying it.

SELECT my_json."label"
FROM xyz efv1
CROSS APPLY OPENJSON(efv1."Value", '$') jvalue
CROSS APPLY OPENJSON(jvalue."Value", '$') 
WITH(       
	"label" NVARCHAR(MAX) '$."label"'	   
) as my_json

Kindly guide me though this.

Azure Data Factory
Azure Data Factory
An Azure service for ingesting, preparing, and transforming data at scale.
10,130 questions
{count} votes