JSON Extraction Functions

There are two extraction functions, which have their respective operators. The operators can only be used if the string is stored as the JSON logical type. These functions support the same two location notations as JSON Scalar functions.

Note that the arrow operator ->, which is used for JSON extracts, has a low precedence as it is also used in lambda functions. Therefore, you need to surround the -> operator with parentheses when expressing operations such as equality comparisons (=). For example:

SELECT ((JSON '{"field": 42}')->'field') = 42;

The low precedence also applies when an extraction is combined with other conditions in a WHERE clause: AND and OR bind more tightly than the arrow operators, so the condition preceding the extraction is absorbed into the arrow operator's left operand, and the query fails with an error message that does not mention precedence. For example:

SELECT count(*) FROM (VALUES ('{}'::JSON)) t(j)
WHERE 1 = 1 AND j->>'field' IS NOT NULL;

Conversion Error:
Failed to cast value to numerical: {} when casting from source column j

Here, the condition is parsed as ((1 = 1 AND j)->>'field') IS NOT NULL. Parenthesizing the extraction, i.e., (j->>'field') IS NOT NULL, or using the equivalent function instead of the operator, i.e., json_extract_string(j, 'field') IS NOT NULL, avoids the issue. In DuckDB v2.0.0 and later, the arrow operators bind more tightly and this example works without parentheses.

Warning DuckDB's JSON data type uses 0-based indexing.

Examples:

CREATE TABLE example (j JSON);
INSERT INTO example VALUES
    ('{ "family": "anatidae", "species": [ "duck", "goose", "swan", null ] }');

SELECT json_extract(j, '$.family') FROM example;

"anatidae"

SELECT j->'$.family' FROM example;

"anatidae"

SELECT j->'$.species[0]' FROM example;

"duck"

SELECT j->'$.species[*]' FROM example;

["duck", "goose", "swan", null]

SELECT j->>'$.species[*]' FROM example;

[duck, goose, swan, null]

SELECT j->'$.species'->0 FROM example;

"duck"

SELECT j->'species'->['/0', '/1'] FROM example;

['"duck"', '"goose"']

SELECT json_extract_string(j, '$.family') FROM example;

anatidae

SELECT j->>'$.family' FROM example;

anatidae

SELECT j->>'$.species[0]' FROM example;

duck

SELECT j->'species'->>0 FROM example;

duck

SELECT j->'species'->>['/0', '/1'] FROM example;

[duck, goose]

Note that DuckDB's JSON data type uses 0-based indexing.

If multiple values need to be extracted from the same JSON, it is more efficient to extract a list of paths:

The following will cause the JSON to be parsed twice:

Resulting in a slower query that uses more memory:

SELECT
    json_extract(j, 'family') AS family,
    json_extract(j, 'species') AS species
FROM example;

The following produces the same result but is faster and more memory-efficient:

WITH extracted AS (
    SELECT json_extract(j, ['family', 'species']) AS extracted_list
    FROM example
)
SELECT
    extracted_list[1] AS family,
    extracted_list[2] AS species
FROM extracted;

Processing Extracted Arrays with Lambda Functions

When a path with a wildcard such as [*] extracts a JSON array into a LIST, the result can be processed further with lambda functions. For example, use list_transform to uppercase each extracted species name:

SELECT list_transform(j->>'$.species[*]', lambda s: upper(s)) AS names
FROM example;

To drop the NULL entries and combine the remaining names into a single string, chain list_filter and list_reduce:

SELECT list_reduce(
    list_filter(j->>'$.species[*]', lambda s: s IS NOT NULL),
    lambda acc, s: acc || ', ' || s
) AS names
FROM example;

JSON Scalar Functions

The following scalar JSON functions can be used to gain information about the stored JSON values. With the exception of json_valid(json), all JSON functions produce an error when invalid JSON is supplied.

We support two kinds of notations to describe locations within JSON: JSON Pointer and JSONPath.

The JSONPointer syntax separates each field with a /. For example, to extract the first element of the array with key duck, you can do:

SELECT json_extract('{"duck": [1, 2, 3]}', '/duck/0');

1

The JSONPath syntax separates fields with a ., and accesses array elements with [i], and always starts with $. Using the same example, we can do the following:

SELECT json_extract('{"duck": [1, 2, 3]}', '$.duck[0]');

1

Note that DuckDB's JSON data type uses 0-based indexing.

JSONPath is more expressive, and can also access from the back of lists:

SELECT json_extract('{"duck": [1, 2, 3]}', '$.duck[#-1]');

3

JSONPath also allows escaping syntax tokens, using double quotes:

SELECT json_extract('{"duck.goose": [1, 2, 3]}', '$."duck.goose"[1]');

2

Examples using the anatidae biological family:

CREATE TABLE example (j JSON);
INSERT INTO example VALUES
    ('{ "family": "anatidae", "species": [ "duck", "goose", "swan", null ] }');

SELECT json(j) FROM example;

{"family":"anatidae","species":["duck","goose","swan",null]}

SELECT j.family FROM example;

"anatidae"

SELECT j.species[0] FROM example;

"duck"

SELECT json_valid(j) FROM example;

true

SELECT json_valid('{');

false

SELECT json_array_length('["duck", "goose", "swan", null]');

4

SELECT json_array_length(j, 'species') FROM example;

4

SELECT json_array_length(j, '/species') FROM example;

4

SELECT json_array_length(j, '$.species') FROM example;

4

SELECT json_array_length(j, ['$.species']) FROM example;

[4]

SELECT json_type(j) FROM example;

OBJECT

SELECT json_keys(j) FROM example;

[family, species]

SELECT json_structure(j) FROM example;

{"family":"VARCHAR","species":["VARCHAR"]}

SELECT json_structure('["duck", {"family": "anatidae"}]');

["JSON"]

Because JSON has no native temporal or UUID types, json_structure reports DATE, TIME, TIMESTAMP, and UUID values as VARCHAR, since that is how they are represented in JSON. If you feed this structure back into from_json, these columns are produced as VARCHAR. To recover the original types, replace the relevant "VARCHAR" entries in the structure with the desired type name, for example "DATE".

SELECT json_contains('{"key": "value"}', '"value"');

true

SELECT json_contains('{"key": 1}', '1');

true

SELECT json_contains('{"top_key": {"key": "value"}}', '{"key": "value"}');

true

JSON Patching and Normalization Functions

The following scalar functions compute, apply and clean up JSON patches. They complement the json_merge_patch function, which applies a RFC 7396 merge patch to a JSON document.

Compute the patch between two documents:

SELECT json_merge_patch_diff('{"a": 1, "b": 2, "c": 3}', '{"a": 1, "b": 99, "d": 4}');

{"c":null,"b":99,"d":4}

Applying the diff using json_merge_patch reconstructs the modified document:

SELECT json_merge_patch(
    '{"a": 1, "b": 2, "c": 3}',
    json_merge_patch_diff('{"a": 1, "b": 2, "c": 3}', '{"a": 1, "b": 99, "d": 4}')
) = '{"a":1,"b":99,"d":4}' AS round_trips;

true

json_deep_merge treats JSON null values in the patch as “keep the original value”, while json_merge_patch deletes the key:

SELECT
    json_merge_patch('{"a": {"x": 1, "y": 2}}', '{"a": {"y": null, "z": 3}}') AS merge_patch,
    json_deep_merge('{"a": {"x": 1, "y": 2}}', '{"a": {"y": null, "z": 3}}') AS deep_merge;

json_deep_merge accepts any number of patches:

SELECT json_deep_merge('{"a": 1}', '{"a": null}', '{"a": 2}');

{"a":2}

Normalize the key order of a document, e.g., to compare or hash documents that only differ in key order:

SELECT json_normalize('{"c": {"b": {"z": 1, "a": 2}, "a": 3}, "a": 4}');

{"a":4,"c":{"a":3,"b":{"a":2,"z":1}}}

SELECT md5(json_normalize('{"z": 1, "a": 2}')) = md5(json_normalize('{"a": 2, "z": 1}')) AS same_hash;

true

Remove null-valued keys recursively:

SELECT json_strip_nulls('{"a": [{"x": 1, "y": null}, {"z": null}], "b": null}');

{"a":[{"x":1},{}]}

JSON Aggregate Functions

There are three JSON aggregate functions.

Examples:

CREATE TABLE example1 (k VARCHAR, v INTEGER);
INSERT INTO example1 VALUES ('duck', 42), ('goose', 7);

SELECT json_group_array(v) FROM example1;

[42, 7]

SELECT json_group_object(k, v) FROM example1;

{"duck":42,"goose":7}

CREATE TABLE example2 (j JSON);
INSERT INTO example2 VALUES
    ('{"family": "anatidae", "species": ["duck", "goose"], "coolness": 42.42}'),
    ('{"family": "canidae", "species": ["labrador", "bulldog"], "hair": true}');

SELECT json_group_structure(j) FROM example2;

{"family":"VARCHAR","species":["VARCHAR"],"coolness":"DOUBLE","hair":"BOOLEAN"}

Transforming JSON to Nested Types

In many cases, it is inefficient to extract values from JSON one-by-one. Instead, we can “extract” all values at once, transforming JSON to the nested types LIST and STRUCT.

The structure argument is JSON of the same form as returned by json_structure. The structure argument can be modified to transform the JSON into the desired structure and types. It is possible to extract fewer key/value pairs than are present in the JSON, and it is also possible to extract more: missing keys become NULL.

Examples:

CREATE TABLE example (j JSON);
INSERT INTO example VALUES
    ('{"family": "anatidae", "species": ["duck", "goose"], "coolness": 42.42}'),
    ('{"family": "canidae", "species": ["labrador", "bulldog"], "hair": true}');

SELECT json_transform(j, '{"family": "VARCHAR", "coolness": "DOUBLE"}') FROM example;

{'family': anatidae, 'coolness': 42.420000}
{'family': canidae, 'coolness': NULL}

SELECT json_transform(j, '{"family": "TINYINT", "coolness": "DECIMAL(4, 2)"}') FROM example;

{'family': NULL, 'coolness': 42.42}
{'family': NULL, 'coolness': NULL}

SELECT json_transform_strict(j, '{"family": "TINYINT", "coolness": "DOUBLE"}') FROM example;

Invalid Input Error:
Failed to cast value: "anatidae"

JSON Table Functions

DuckDB implements two JSON table functions that take a JSON value and produce a table from it.

If the element is not an array or object, the element itself is returned. If the optional path argument is supplied, traversal starts from the element at the given path instead of the root element.

The resulting table has the following columns:

These functions are analogous to SQLite's functions with the same name. Note that, because the json_each and json_tree functions refer to previous subqueries in the same FROM clause, they are lateral joins.

Examples:

CREATE TABLE example (j JSON);
INSERT INTO example VALUES
    ('{"family": "anatidae", "species": ["duck", "goose"], "coolness": 42.42}'),
    ('{"family": "canidae", "species": ["labrador", "bulldog"], "hair": true}');

SELECT je.*, je.rowid
FROM example AS e, json_each(e.j) AS je;

SELECT je.*, je.rowid
FROM example AS e, json_each(e.j, '$.species') AS je;

SELECT je.key, je.value, je.type, je.id, je.parent, je.fullkey, je.rowid
FROM example AS e, json_tree(e.j) AS je;