Data Import
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](https://datatracker.ietf.org/doc/html/rfc6901) and JSONPath.  
| Function | Description |
|:---|:----|
| `json_array_length(json[, path])` | Return the number of elements in the JSON array `json`, or `0` if it is not a JSON array. If `path` is specified, return the number of elements in the JSON array at the given `path`. If `path` is a `LIST`, the result will be `LIST` of array lengths. |
| `json_contains(json_haystack, json_needle)` | Returns `true` if `json_needle` is contained in `json_haystack`. Both parameters are of JSON type, but `json_needle` can also be a numeric value or a string, however the string must be wrapped in double quotes. |
| `json_keys(json[, path])` | Returns the keys of `json` as a `LIST` of `VARCHAR`, if `json` is a JSON object. If `path` is specified, return the keys of the JSON object at the given `path`. If `path` is a `LIST`, the result will be `LIST` of `LIST` of `VARCHAR`. |
| `json_structure(json)` | Return the structure of `json`. Defaults to `JSON` if the structure is inconsistent (e.g., incompatible types in an array). |
| `json_type(json[, path])` | Return the type of the supplied `json`, which is one of `ARRAY`, `BIGINT`, `BOOLEAN`, `DOUBLE`, `OBJECT`, `UBIGINT`, `VARCHAR`, and `NULL`. If `path` is specified, return the type of the element at the given `path`. If `path` is a `LIST`, the result will be `LIST` of types. |
| `json_valid(json)` | Return whether `json` is valid JSON. |
| `json(json)` | Parse and minify `json`. |  
The JSONPointer syntax separates each field with a `/`.
For example, to extract the first element of the array with key `duck`, you can do:  
```sql
SELECT json_extract('{"duck": [1, 2, 3]}', '/duck/0');
```  
```text
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:  
```sql
SELECT json_extract('{"duck": [1, 2, 3]}', '$.duck[0]');
```  
```text
1
```  
Note that DuckDB's JSON data type uses [0-based indexing](#::indexing).  
JSONPath is more expressive, and can also access from the back of lists:  
```sql
SELECT json_extract('{"duck": [1, 2, 3]}', '$.duck[#-1]');
```  
```text
3
```  
JSONPath also allows escaping syntax tokens, using double quotes:  
```sql
SELECT json_extract('{"duck.goose": [1, 2, 3]}', '$."duck.goose"[1]');
```  
```text
2
```  
Examples using the [anatidae biological family](https://en.wikipedia.org/wiki/Anatidae):  
```sql
CREATE TABLE example (j JSON);
INSERT INTO example VALUES
('{ "family": "anatidae", "species": [ "duck", "goose", "swan", null ] }');
```  
```sql
SELECT json(j) FROM example;
```  
```text
{"family":"anatidae","species":["duck","goose","swan",null]}
```  
```sql
SELECT j.family FROM example;
```  
```text
"anatidae"
```  
```sql
SELECT j.species[0] FROM example;
```  
```text
"duck"
```  
```sql
SELECT json_valid(j) FROM example;
```  
```text
true
```  
```sql
SELECT json_valid('{');
```  
```text
false
```  
```sql
SELECT json_array_length('["duck", "goose", "swan", null]');
```  
```text
4
```  
```sql
SELECT json_array_length(j, 'species') FROM example;
```  
```text
4
```  
```sql
SELECT json_array_length(j, '/species') FROM example;
```  
```text
4
```  
```sql
SELECT json_array_length(j, '$.species') FROM example;
```  
```text
4
```  
```sql
SELECT json_array_length(j, ['$.species']) FROM example;
```  
```text
[4]
```  
```sql
SELECT json_type(j) FROM example;
```  
```text
OBJECT
```  
```sql
SELECT json_keys(j) FROM example;
```  
```text
[family, species]
```  
```sql
SELECT json_structure(j) FROM example;
```  
```text
{"family":"VARCHAR","species":["VARCHAR"]}
```  
```sql
SELECT json_structure('["duck", {"family": "anatidae"}]');
```  
```text
["JSON"]
```  
```sql
SELECT json_contains('{"key": "value"}', '"value"');
```  
```text
true
```  
```sql
SELECT json_contains('{"key": 1}', '1');
```  
```text
true
```  
```sql
SELECT json_contains('{"top_key": {"key": "value"}}', '{"key": "value"}');
```  
```text
true
```