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

by

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).

Syntax

The basic syntax goes like this:

json_keys(json[, path])

Parameters:

  • json: A string containing a JSON object.
  • path(optional): A JSON path string (e.g., '$.store.book') or a list of path strings. If provided, json_keys() returns the keys of the object at the specified path.
    • If path is a single string, the result is a single LIST of VARCHAR.
    • If path is a LIST of path strings, the result is a LIST of LIST of VARCHAR (one list of keys for each path).

Return Type

  • Returns a LIST of VARCHAR if no path or a single path is provided.
  • Returns a LIST of LIST of VARCHAR if a list of paths is provided.

Usage Examples

Here are some examples that demonstrate how it works.

Example 1: Basic Usage

Extract keys from a top-level JSON object.

SELECT json_keys('{"name": "Tyler", "age": 30, "city": "Paris"}') AS keys;

Result:

+-------------------+
|       keys        |
+-------------------+
| [name, age, city] |
+-------------------+

Example 2: Extracting Keys from a Nested Object

We can use a JSON path to target a nested object.

SELECT json_keys('{"user": {"id": 1, "email": "[email protected]"}}', '$.user') AS keys;

Result:

+-------------+
|    keys     |
+-------------+
| [id, email] |
+-------------+

If the path doesn’t exist, NULL is returned:

SELECT json_keys('{"user": {"id": 1, "email": "[email protected]"}}', '$.profile') AS keys;

Result:

+------+
| keys |
+------+
| NULL |
+------+

Example 3: Extracting Keys with Multiple Paths

We can use a list of paths to extract keys from multiple nested objects.

SELECT json_keys(
  '{"a": {"x": 1, "y": 2}, "b": {"m": 3, "n": 4}}',
  ['$.a', '$.b']
) AS keys;

Result:

+------------------+
|       keys       |
+------------------+
| [[x, y], [m, n]] |
+------------------+

In this case, a LIST of LIST of VARCHAR is returned.

Example 4: Non-object at Path

If the value at the specified path is not a JSON object, the function returns an empty list:

SELECT json_keys('{"scores": [1,2,3]}', '$.scores') AS keys;

Result:

+------+
| keys |
+------+
| []   |
+------+

Example 5: Passing the Wrong Argument Type

If the first argument isn’t a JSON type, an error is returned:

SELECT json_keys('age') AS keys;

Result:

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

If I pass a JSON type, but not a JSON object, then I get an empty list:

SELECT json_keys('"age"') AS keys;

Result:

+------+
| keys |
+------+
| []   |
+------+