Azure Devops Git: Extract SQL query from JSON file?

Question

I'm commiting SQL script files to an Azure Devops Git repository. This is from within Synapse Serverless.

It works OK for restoring old versions of the SQL Scripts.

But sometimes I just want to see the old SQL script, or maybe grab it and run it manually.

The scripts are all there, but saved as JSON.

Open one up, and the query itself is all one line - and copy/paste doesn't even work due to the carriage return and linebreak symbols

I was able to write a Python script that will extract & format the SQL. But:

1. It requires downloading the JSON file / pointing the script at the file.
2. It...requires running a Python Script. It feels like there should be an easier, built-in-ish way that I'm missing.

Pasting code here, just in case it helps someone else who stumbles upon this post. Though again, I'm wondering if there isn't an easier way.

import json
import sqlparse

# Load the JSON file
with open(r"C:\YourPathToFile\TheFile.json") as f:
    data = json.load(f)

# Extract the SQL code from the JSON data
sql_code = data['properties']['content']['query']

# Replace the newline and carriage return characters
sql_code = sql_code.replace('\\n', '\n').replace('\\r', '\r')

# Format the SQL code
formatted_sql = sqlparse.format(sql_code, reindent=True, keyword_case='upper')

# Print the formatted SQL code
print(formatted_sql)

Answer 1

Azure Git Repos is just a version control tool based on Git that help you to manage the files versions. It is not able to automatically parse and extract the SQL scripts from JSON file. There also is not built-in feature to do this.

To parse and extract the SQL scripts from JSON file, just like as you have been doing, you need to develop a script/application (can be written by any of PowerShell, Bash, Python, etc..) to read the content of JSON file, then parse and extract the SQL scripts from the content.