DuckDB Client and Hardware

Oct 13, 2025 by thesweetdesigns.com 147 views

Introduction

In this article, we will discuss a critical issue with the json_object function in DuckDB, a popular open-source SQL database. The issue arises when the json_object function is used to create a JSON object with a string value that is too long. In such cases, the function produces an unescaped linebreak in the string value, resulting in malformed JSON. We will explore the steps to reproduce this issue, the operating system and DuckDB version involved, and the necessary configuration to replicate the problem.

What Happens?

When the json_object function is called with a string value that is too long, it produces an unescaped linebreak in the string value. This linebreak is not properly escaped, resulting in malformed JSON. This issue can have significant consequences, especially when working with large datasets or complex JSON structures.

To Reproduce

To reproduce this issue, follow these steps:

Step 1: Load the TPCH Dataset

LOAD tpch;

Step 2: Install the TPCH Schema

INSTALL tpch;

Step 3: Generate Sample Data

CALL dbgen(sf=0);

Step 4: Set the Output Mode to Line

.mode line

Step 5: Run the Query

SELECT json_object('abcde', query) FROM tpch_queries() WHERE query_nr = 20;

Copy the query result and paste it in a text file. Notice that the JSON value is a single line (good).

Step 6: Repeat the Query

Repeat the exact same query again.

Copy the result and paste it in a text file. Notice that now the JSON value contains an actual, unescaped CRLF pair.

OS and DuckDB Version

The issue was observed on the following operating system and DuckDB version:

Windows 11 Enterprise
DuckDB Version: v 1.2.1 GA, v1.3.0-dev1112 981c7a8573

DuckDB Client and Hardware

The issue was tested using the following DuckDB client and hardware:

DuckDB Client: CLI
Hardware: No response

Reporter Information

The issue was reported by:

Full Name: Roland Bouman
Affiliation: EPAM Systems BV

Latest Build Tested

The issue was tested with the latest nightly build.

Relevant Data Sets and Code

Yes, all relevant data sets and code required to reproduce the issue were included.

Relevant Configuration

Yes, all relevant configuration, including CPU architecture, Python version, and Linux distribution, were included.

Conclusion

In conclusion, the json_object function in DuckDB can produce unescaped linebreaks in string values, resulting in malformed JSON. This issue can be reproduced by following the steps outlined above. It is essential to be aware of this issue and take necessary precautions when working with large datasets or complex JSON structures. We recommend testing with the latest nightly build and including all relevant data sets, code, and configuration to reproduce the issue.

Recommendations

To avoid this issue, we recommend the following:

Use the latest nightly build of DuckDB.
Be aware of the length of string values when using the json_object function.
Use proper escaping techniques when working with JSON values.
Include all relevant data sets, code, and configuration when reporting issues.

Introduction

In our previous article, we discussed a critical issue with the json_object function in DuckDB, a popular open-source SQL database. The issue arises when the json_object function is used to create a JSON object with a string value that is too long. In such cases, the function produces an unescaped linebreak in the string value, resulting in malformed JSON. In this article, we will answer some frequently asked questions related to this issue.

Q: What is the cause of this issue?

A: The cause of this issue is the way the json_object function handles string values that are too long. When the function encounters a string value that exceeds a certain length, it produces an unescaped linebreak in the string value, resulting in malformed JSON.

Q: How can I reproduce this issue?

A: To reproduce this issue, follow the steps outlined in our previous article:

Load the TPCH dataset using the LOAD command.
Install the TPCH schema using the INSTALL command.
Generate sample data using the dbgen command.
Set the output mode to line using the .mode command.
Run the query using the SELECT command.

Q: What operating systems and DuckDB versions are affected by this issue?

A: The issue was observed on the following operating systems and DuckDB versions:

Windows 11 Enterprise
DuckDB Version: v 1.2.1 GA, v1.3.0-dev1112 981c7a8573

Q: Can I use the latest nightly build to resolve this issue?

A: Yes, using the latest nightly build of DuckDB can help resolve this issue. However, it is essential to test with the latest nightly build and include all relevant data sets, code, and configuration to reproduce the issue.

Q: How can I avoid this issue in the future?

A: To avoid this issue in the future, follow these best practices:

Use the latest nightly build of DuckDB.
Be aware of the length of string values when using the json_object function.
Use proper escaping techniques when working with JSON values.
Include all relevant data sets, code, and configuration when reporting issues.

Q: Can I use a different JSON library to resolve this issue?

A: Yes, you can use a different JSON library to resolve this issue. However, it is essential to ensure that the new library is compatible with your DuckDB version and operating system.

Q: How can I report this issue to the DuckDB community?

A: To report this issue to the DuckDB community, follow these steps:

Create a new issue on the DuckDB GitHub page.
Include all relevant data sets, code, and configuration.
Describe the issue and its symptoms.
Provide any relevant error messages or logs.

By following these steps, you can ensure that your issue is properly reported and addressed by the DuckDB community.

Conclusion

In conclusion, the json_object function in DuckDB can produce unescaped linebreaks in string values, resulting in malformed JSON. By following the best practices outlined in this article, you can avoid this issue in the future and ensure that your JSON data is properly formatted and free from errors.