question

MichaelColeman-6206 avatar image
0 Votes"
MichaelColeman-6206 asked AnnuKumari-MSFT commented

ADLS2 Storage [ ] wrapped json file query from Azure Synapse Serverless

I am using ADLS2 Storage for json files and am trying to read the file from serverless Synapse Analytics.

I can read the file using the normal means (SQL SCRIPT AND JSON_VALUE(doc, '$.date_rep') AS date_reported,) .

THE PROBLEM : The json file has [ ] wrapping the file contents. I cannot modify the files but cannot read using a query because the file is [] wrapped.

How do I remove the square brackets from the beginning and end of the json file in the query ( json_modify , ect)

azure-synapse-analytics
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

DavidBroggy-5270 avatar image
0 Votes"
DavidBroggy-5270 answered

Hi @MichaelColeman-6206
Are you able to use jq to unwrap the json?
eg:
json-and-jq



If this helps please accept my solution and upvote.
Or just have a nice day.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

AnnuKumari-MSFT avatar image
0 Votes"
AnnuKumari-MSFT answered AnnuKumari-MSFT commented

Hi @MichaelColeman-6206,
Welcome to Microsoft Q&A platform and thanks for posting your query.

As I understand your requirement here, you want to remove the square brackets from start and end of the JSON content. As you mentioned you are using Azure syanpse analytics , I hope you can leverage Syanpse pipelines in order to achieve the requirement.

You can follow the below steps :
Step1: Create a dataset pointing to the json file in ADLS.
Step2: Use LookUp activity in Synapse pipeline and use the newly created dataset in the lookup to fetch the JSON content as the LookUp output.
Step3: Create a variable in the pipeline say myvar and pass the following expression in its value: @substring(string(variables('var')),add(indexof(variables('var'),'"'),1),add(lastindexof(variables('var'),'"'),-2)) .

Output of the set variable activity will replace the brackets of the source JSON .

196477-image.png

196506-image.png

196552-image.png

Hope this will help. Please let us know if any further queries.


  • Please don't forget to click on 130616-image.png or upvote 130671-image.png button whenever the information provided helps you.
    Original posters help the community find answers faster by identifying the correct answer. Here is how

  • Want a reminder to come back and check responses? Here is how to subscribe to a notification

  • If you are interested in joining the VM program and help shape the future of Q&A: Here is how you can be part of Q&A Volunteer Moderators




image.png (40.6 KiB)
image.png (49.1 KiB)
image.png (13.2 KiB)
· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @MichaelColeman-6206 ,
Just checking in to see if the above answer helped. Please do consider clicking Accept Answer and Up-Vote for the same as accepted answers help community as well. If you have any further query do let us know.

0 Votes 0 ·

Hi @MichaelColeman-6206 ,
Just following up to see if the above answer helped. Please do consider clicking Accept Answer and Up-Vote for the same as accepted answers help community as well. If you have any further query do let us know.

0 Votes 0 ·