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

Question

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

Pratisti Satardekar 130

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.

phemanth 15,570 Reputation points Microsoft External Staff Moderator

2023-12-01T12:18:23.9833333+00:00
@Pratisti Satardekar

Thanks For Reaching out MS Q&A

JSON Schema: ADF might not be able to parse the JSON schema correctly. This could be due to additional brackets being added or the data type being set incorrectly when copying the JSON into the blob storage. You might want to check the sink setting and the data type you used when copying the JSON.

You can try validating the JSON text using an online JSON validator tool to ensure that it is properly formatted.

If the JSON text is valid, you can try modifying the query to handle the JSON array format. You can use the OPENJSON function with the WITH clause to extract the required fields from the JSON objects. Here's an example query that should work with the JSON data you shared:SQL

SELECT my_json."label" FROM xyz efv1 CROSS APPLY OPENJSON('[{"label":"New drop Option 1","value":"key-1","key":"key-1"}]') jvalue WITH( "label" NVARCHAR(MAX) '$."label"' ) as my_json

Replace the JSON text in the OPENJSON function with your actual JSON data. This query should extract the "label" field from the JSON objects in the array.

Null Values: If your JSON data contains null values, ADF data flow might fail to read the JSON objects. You could consider using a custom JSON schema in the data flow that can handle null values.

Error Handling: You might want to set the sink error row handling to “Continue on Error” when processing database data. This is an automated catch-all method that does not require custom logic in your data flow.

Logging Errors: You can capture the error using a set variable activity in Azure Data Factory. Use the expression @activity ('Data Flow').Error.message to capture the error message using Set Variable activity4. Later, you can store the error message in blob storage for future reference using a copy activity.

I hope this helps! Let us know if you have any further questions.
phemanth 15,570 Reputation points Microsoft External Staff Moderator

2023-12-04T06:42:20.7933333+00:00

@Pratisti Satardekar We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.
phemanth 15,570 Reputation points Microsoft External Staff Moderator

2023-12-05T11:11:49.28+00:00

@Pratisti Satardekar just checking back to see if you have a resolution yet. In case if you have any resolution please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.

Your answer

phemanth 15,570 Reputation points Microsoft External Staff Moderator

2023-12-01T12:18:23.9833333+00:00

@Pratisti Satardekar

Thanks For Reaching out MS Q&A

JSON Schema: ADF might not be able to parse the JSON schema correctly. This could be due to additional brackets being added or the data type being set incorrectly when copying the JSON into the blob storage. You might want to check the sink setting and the data type you used when copying the JSON.

You can try validating the JSON text using an online JSON validator tool to ensure that it is properly formatted.

If the JSON text is valid, you can try modifying the query to handle the JSON array format. You can use the OPENJSON function with the WITH clause to extract the required fields from the JSON objects. Here's an example query that should work with the JSON data you shared:SQL

SELECT my_json."label" FROM xyz efv1 CROSS APPLY OPENJSON('[{"label":"New drop Option 1","value":"key-1","key":"key-1"}]') jvalue WITH( "label" NVARCHAR(MAX) '$."label"' ) as my_json

Replace the JSON text in the OPENJSON function with your actual JSON data. This query should extract the "label" field from the JSON objects in the array.

Null Values: If your JSON data contains null values, ADF data flow might fail to read the JSON objects. You could consider using a custom JSON schema in the data flow that can handle null values.

Error Handling: You might want to set the sink error row handling to “Continue on Error” when processing database data. This is an automated catch-all method that does not require custom logic in your data flow.

Logging Errors: You can capture the error using a set variable activity in Azure Data Factory. Use the expression @activity ('Data Flow').Error.message to capture the error message using Set Variable activity4. Later, you can store the error message in blob storage for future reference using a copy activity.

I hope this helps! Let us know if you have any further questions.
phemanth 15,570 Reputation points Microsoft External Staff Moderator

2023-12-04T06:42:20.7933333+00:00

@Pratisti Satardekar We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.
phemanth 15,570 Reputation points Microsoft External Staff Moderator

2023-12-05T11:11:49.28+00:00

@Pratisti Satardekar just checking back to see if you have a resolution yet. In case if you have any resolution please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.

Share via

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

Your answer