A Fine Slice Of SQL Server
Gilbert Quevauvilliers wants to know who’s in your group:
Using a Service Principal to get all Entra ID Group Members into JSON File using a Python Notebook
Sometimes it is useful to get all Group Members into a JSON file so that this could be used for reporting purposes.
Click through for the instructions.
Comments closedLike XML, JSON is an open standard storage format for data, metadata, parameters, or other unstructured or semi-structured data. Because of its heavy usage in applications today, it inevitably will make its way into databases where it will need to be stored, compressed, modified, searched, and retrieved.
Even though a relational database is not the ideal place to store and manage less structured data, application requirements can oftentimes override an “optimal” database design. There is a convenience in having JSON data close to related relational data and architecting its storage effectively from the start can save significant time and resources in the future.
Read on for plenty of examples and tips. Ideologically, I have no problem parsing JSON to load data into SQL Server. I have no real problem storing data in JSON if the calling application takes that JSON as-is and does not expect the database to modify or shred that JSON. I have no problem taking relational data and creating JSON structures to send out to calling applications. My problem comes when you store the data as JSON but then expect the database to manage data. Treat the JSON blob as atomic and we’re fine; otherwise, I want to make that data relational, as befits a relational database.
2 CommentsAndrea Gnemmi covers a pair of data types to manage one thing:
We have all encountered the need to store non-structured or semi-structured data in an RDBMS; XML or JSON data in particular. This can be complicated, especially in the past with limited technical options, and even more complicated if we want to query this data efficiently.
Read on to learn more about the differences between JSON and JSONB, as well as mechanisms you can use to query subsets of the data.
Comments closedLukas Eder notes a bit of ANSI SQL:
The following aggregate function computes the number of rows per group which satifsy the
FILTERclause:
SELECTCOUNT(*) FILTER (WHEREBOOK.TITLELIKE'A%'),COUNT(*) FILTER (WHEREBOOK.TITLELIKE'B%'),...FROMBOOKThis is useful for pivot style queries, where multiple aggregate values are computed in one go. For most basic types of aggregate function, it can be emulated simply by using
CASEexpressions, because standard aggregate functions ignoreNULLvalues when aggregating.
Lukas shows how you can also implement this logic using JSON_TRANSFORM() though I think I’d just as soon stick with COUNT(CASE WHEN BOOK.TITLE LIKE 'A%' THEN 1 END) and try hard not to think about shredding JSON.
Joe Obbish has a public service announcement:
I found a few stored procedures with single plan cache entries over 500 MB of memory each according to sys.dm_exec_cached_plans while optimizing the plan cache in production. The stored procedures were relatively simple and both execution plans were less than 1 MB when saved as sqlplan files. I was confused, offended, and confused.
Read on to learn how this happened, see an example of it in action, and how you can avoid it in your own environment.
Comments closedHubert Lubaczewski makes note of some forthcoming functionality:
Back in March/April of 2022 Andres Dunstan committed a series of patches that added support for lots of really interesting features from SQL/JSON standard.
While I’m not avid user of json in database, I was very, very happy. Wrote couple of blogposts about it.
Then, around six month later they got reverted.
Lately, since last year, actually, these re-appeared again:
Read on for the list of commits and the current state of things with respect to implementing the SQL/JSON standard in Postgres.
Comments closedTom Martens performs two of the three R’s:
JSON is a straightforward, text-based data-interchange format fully independent of any programming language. This simplicity is why JSON documents have become so widespread.
Often the result of querying a REST API is returned as a JSON document, but this is not the only use case why I consider it important to get familiar with reading from and writing to a JSON file. I think a JSON document is ideal for ingesting values into a Python script or any code, values that are helpful to control the logic and behavior of the script. But of course, it’s also easy to store the value of a variable inside the JSON document, where this value will be used when the script is running again.
Click through for some Python code to perform these operations. There are about a dozen other methods you can use as well, but this is one of the best.
Comments closedSandeep Pawar wants readability:
JSON is ubiquitous, particularly when working with APIs and logs. Its unstructured nature makes it highly flexible for handling anything from a simple array to a complex nested structure. However, this can also make it challenging for data analysis. When parsing JSON, it’s crucial to understand its structure so you can flatten it and convert it into a tabular format for analysis. Once the structure is identified, you can use pandas or PySpark to explode or normalize it into the desired shape. In this article, I will explain the method I use. While this approach is applicable to any notebook, there is a specific trick to make it work in a Fabric notebook.
Read on for that trick.
Comments closedUmachandar Jayachandran (or, as we all know him, UC) makes an announcement:
We are excited to announce the private preview of native JSON type and JSON_OBJECTAGG & JSON_ARRAYAGG aggregates in Azure SQL Database. The JSON type will allow you to store JSON documents in a native binary format that is optimized for storage and query performance. The ANSI SQL compatible JSON aggregates – JSON_OBJECTAGG & JSON_ARRAYAG will allow you to aggregate relational data and transform the data into JSON documents in a query.
I do have to admit that the native JSON type was a bit curious, given that they had assiduously rejected the notion of introducing a native JSON type for years, yet here we are. But if there are significant enough performance gains—and there can be by moving from text to binary JSON—it can be worth it. The XML type also allowed you to create indexes, which is probably easier to do with a native type.
Comments closedDevang Shah does some flattening and moving:
In the world of IoT devices, industrial historians, infrastructure and application logs, and metrics, machine-generated or software-generated telemetry, there are often scenarios where the upstream data producer produces data in non-standard schemas, formats, and structures that often make it difficult to analyze the data contained in these at scale. Azure Data Explorer provides some useful features to run meaningful, fast, and interactive analytics on such heterogenous data structures and formats.
In this blog, we’re taking an example of a complex JSON file as shown in the screenshot below. You can access the JSON file from this GitHub page to try the steps below.
Click through for the example, which is definitely non-trivial.
Comments closed