json – Page 2

How to Export SQL Server Query Results as JSON with FOR JSON AUTO

August 28, 2025 by Ian

SQL Server makes it surprisingly simple to generate JSON directly from a query. If you’ve ever had to send data to a web service, feed an API, or just save query results in a structured text format, JSON is a natural choice. Instead of writing complex formatting logic in your application, you can let SQL Server do the heavy lifting with the FOR JSON clause. And you can use the AUTO keyword to have the JSON formatted automatically by SQL Server.

In this article, we’ll look at how FOR JSON AUTO works, along with some examples to demonstrate its usage.

7 Ways to Extract Data from JSON in DuckDB

August 22, 2025 by Ian

Most DuckDB distributions come with the JSON extension, and this extension is loaded upon first use. That means we can go ahead and run queries against JSON data right out of the box. One common task we’ll face when working with JSON is extracting data from within the JSON documents. This can include extracting scalar values, or extracting nested JSON from within the outer document.

DuckDB provides us with multiple ways to extract such data. The option we use will largely depend on our use case. Either way, here are seven options for extracting data from JSON documents in DuckDB.

OPENJSON() vs JSON_VALUE() When Parsing JSON in SQL Server

February 4, 2026August 21, 2025 by Ian

Working with JSON in SQL Server often comes down to choosing the right function for the job. Two of the most common options are OPENJSON() and JSON_VALUE(). Both are designed to pull data out of JSON documents, but they work in very different ways and are suited to different scenarios. Knowing when to use each one can save time and simplify your queries.

This article breaks down how OPENJSON() and JSON_VALUE() handle JSON parsing, what makes them different, and the situations where one might be a better fit than the other. Whether you are cleaning up nested JSON or just extracting a single value, understanding these functions will help you work more effectively with JSON data in SQL Server.

Convert a Table to a JSON Document with JSON_GROUP_OBJECT() in DuckDB

July 20, 2025 by Ian

In DuckDB, the json_group_object() function is a convenient way to aggregate data into JSON objects by pairing keys and values across rows within groups.

The function is especially useful when we’re transforming tabular data into a more hierarchical or nested JSON structure for web APIs, reporting, or downstream processing. It helps pivot rows into a single JSON object, making the data more compact and easier to consume in applications that require JSON formats.

Using JSON_TRANSFORM_STRICT() to Perform Strict JSON Transformations in DuckDB

July 11, 2025 by Ian

DuckDB provides us with a json_transform_strict() function that works just like the json_transform() function, except that it throws an error when type casting fails. The json_transform() function on the other hand, returns NULL for the respective field in such cases.

Parse and Minify JSON with DuckDB’s JSON() Function

July 8, 2025 by Ian

One of the many JSON functions that DuckDB provides is the json() function, which parses and minifies JSON values. We pass the JSON data to the function, and it parses and minifies the JSON, then returns the result.

Using JSON_KEYS() in DuckDB to Get the Keys in a JSON Document

July 4, 2025 by Ian

DuckDB has a json_keys() function that we can use to extract the keys from a JSON object. It returns the keys as a list of strings (LIST of VARCHAR).

Filtering a Query Based on JSON Path Existence in DuckDB

June 19, 2025 by Ian

Occasionally we might need to filter data based on the existence of a given path in a JSON document in the database. When using DuckDB, we can achieve this outcome with the JSON_EXISTS() function. This function returns true or false, depending on whether the specified path exists. Therefore, we can use it to help us filter our query on that basis.

Using JSON_CONTAINS() in DuckDB to Check if a Value Exists in a JSON Document

June 18, 2025 by Ian

In DuckDB, we can use the json_contains() function to check whether a specified JSON value is contained within another JSON structure. It returns a boolean (TRUE or FALSE) that indicates whether or not the value was found. We can also check for key value pairs, or even a full JSON object within the JSON document.

Convert Column Values to a JSON Array with DuckDB’s JSON_GROUP_ARRAY() Function

June 10, 2025 by Ian

The json_group_array() function in DuckDB is used to aggregate values into a JSON array, making it especially useful when working with structured or semi-structured data such as JSON. This function is part of DuckDB’s JSON extension and works similarly to DuckDB’s string_agg() or the group_concat() function in some other RDBMSs, but instead of returning a delimited string, it returns a well-formed JSON array.

This function is particularly helpful when we need to represent grouped or hierarchical data in a JSON format for export, reporting, or further transformation.