In DuckDB, the json_structure() is used to parse and introspect JSON data, returning the structure of a JSON document. This is especially useful when you’re working with semi-structured JSON data and you want to understand its schema — including nested keys, arrays, and types — without manually inspecting the raw JSON.

Syntax

The syntax is very simple:

json_structure(json)

Where json is the JSON document. This can be provided as JSON or VARCHAR.

Example

Let’s start with a basic example:

SELECT json_structure('{"name": "Tyler", "age": 20}') AS json_structure;

Result:

+------------------------------------+
|           json_structure           |
+------------------------------------+
| {"name":"VARCHAR","age":"UBIGINT"} |
+------------------------------------+

It includes all keys in the document, along with their JSON respective types instead of their values.

Here’s a larger JSON document:

SELECT json_structure('{
    "name": "Frida", 
    "age": 25, 
    "scores": [95, 87, 92],
    "preferences": {
        "theme": "dark",
        "notifications": true
        }, 
    "active": true
    }') AS json_structure;

Result:

+----------------------------------------------------------------------------------------------------------------------------------------+
|                                                             json_structure                                                             |
+----------------------------------------------------------------------------------------------------------------------------------------+
| {"name":"VARCHAR","age":"UBIGINT","scores":["UBIGINT"],"preferences":{"theme":"VARCHAR","notifications":"BOOLEAN"},"active":"BOOLEAN"} |
+----------------------------------------------------------------------------------------------------------------------------------------+

Extracting the Data Type from a Specific Field

Given the result is returned as JSON it’s possible to use JSON functions such as json_extract() against the result. This function extracts the specified value from a JSON document. In this case, the specified value will be a data type:

SELECT json_extract(
    json_structure('{"name": "Tyler", "age": 20}'), '$.age' 
    ) AS age_type;

Result:

+-----------+
| age_type  |
+-----------+
| "UBIGINT" |
+-----------+

Passing a Varchar

The json_structure() function also accepts VARCHAR arguments:

SELECT json_structure('"Tyler"') AS json_structure;

Result:

+----------------+
| json_structure |
+----------------+
| "VARCHAR"      |
+----------------+

But it still needs to conform to JSON type convention. The above example is a JSON string, which is surrounded in double quotes (as per JSON convention). If we remove the double quotes, here’s what happens:

SELECT json_structure('Tyler') AS json_structure;

Result:

Invalid Input Error:
Malformed JSON at byte 0 of input: unexpected character.  Input: Tyler

So, the function was still expecting some form of JSON data.

Here it is with a number:

SELECT json_structure('20') AS json_structure;

Result:

+----------------+
| json_structure |
+----------------+
| "UBIGINT"      |
+----------------+

So we still had to provide single quotes around the argument. Here’s what happens if we remove those:

SELECT json_structure(20) AS json_structure;

Result:

Binder Error:
No function matches the given name and argument types 'json_structure(INTEGER_LITERAL)'. You might need to add explicit type casts.
	Candidate functions:
	json_structure(VARCHAR) -> JSON
	json_structure(JSON) -> JSON


LINE 1: SELECT json_structure(20) AS json_structure;
               ^

An error, as expected. The error message actually provides us with a clue as to the argument types that are accepted; VARCHAR and JSON.