Data Import
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 supports the same two location notations as [JSON Scalar functions](#::json-scalar-functions).  
| Function | Alias | Operator | Description |
|:---|:---|:-|
| `json_exists(json, path)` | | | Returns `true` if the supplied path exists in the `json`, and `false` otherwise. |
| `json_extract(json, path)` | `json_extract_path` | `->` | Extracts `JSON` from `json` at the given `path`. If `path` is a `LIST`, the result will be a `LIST` of `JSON`. |
| `json_extract_string(json, path)` | `json_extract_path_text` | `->>` | Extracts `VARCHAR` from `json` at the given `path`. If `path` is a `LIST`, the result will be a `LIST` of `VARCHAR`. |
| `json_value(json, path)` | | | Extracts `JSON` from `json` at the given `path`. If the `json` at the supplied path is not a scalar value, it will return `NULL`. |  
Note that the equality comparison operator (` =`) has a higher precedence than the `->` JSON extract operator. Therefore, surround the uses of the `->` operator with parentheses when making equality comparisons. For example:  
```sql
SELECT ((JSON '{"field": 42}')->'field') = 42;
```  
> **Warning.  ** DuckDB's JSON data type uses [0-based indexing](#::indexing).  
Examples:  
```sql
CREATE TABLE example (j JSON);
INSERT INTO example VALUES
('{ "family": "anatidae", "species": [ "duck", "goose", "swan", null ] }');
```  
```sql
SELECT json_extract(j, '$.family') FROM example;
```  
```text
"anatidae"
```  
```sql
SELECT j->'$.family' FROM example;
```  
```text
"anatidae"
```  
```sql
SELECT j->'$.species[0]' FROM example;
```  
```text
"duck"
```  
```sql
SELECT j->'$.species[*]' FROM example;
```  
```text
["duck", "goose", "swan", null]
```  
```sql
SELECT j->>'$.species[*]' FROM example;
```  
```text
[duck, goose, swan, null]
```  
```sql
SELECT j->'$.species'->0 FROM example;
```  
```text
"duck"
```  
```sql
SELECT j->'species'->['0','1'] FROM example;
```  
```text
["duck", "goose"]
```  
```sql
SELECT json_extract_string(j, '$.family') FROM example;
```  
```text
anatidae
```  
```sql
SELECT j->>'$.family' FROM example;
```  
```text
anatidae
```  
```sql
SELECT j->>'$.species[0]' FROM example;
```  
```text
duck
```  
```sql
SELECT j->'species'->>0 FROM example;
```  
```text
duck
```  
```sql
SELECT j->'species'->>['0','1'] FROM example;
```  
```text
[duck, goose]
```  
Note that DuckDB's JSON data type uses [0-based indexing](#::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:  
```sql
SELECT
json_extract(j, 'family') AS family,
json_extract(j, 'species') AS species
FROM example;
```  
<div class="narrow_table monospace_table"></div>  
|   family   |           species            |
|------------|------------------------------|
| "anatidae" | ["duck","goose","swan",null] |  
The following produces the same result but is faster and more memory-efficient:  
```sql
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;
```