## Purpose
Data stored in JSON format can be interpreted as a data source using the jsontable keyword.
The expression specifies a master JSON expression within the context of which the rows are evaluated using the column specifications.
The passing option specifies the source of the data in JSON format. The source is often the outcome of a read_file() table function or a URL download using httpget().
The columns are specified using their JSON path relative to the master path.
```sql
select json.*
from ( select '{ "name":"John", "age":30, "cars": { "car1":"Ford", "car2":"BMW", "car3":"Fiat"}
}' json
from dual@datadictionary d
--
-- Generate 25 copies.
--
join range@datadictionary(25, 1) r
) jsondata
join jsontable
( ''
passing jsondata.json
columns orderName varchar2 path 'name'
) json
```
## Syntax
```mermaid
%%{init: {
'theme': 'base',
'flowchart': { 'padding': '7', 'nodeSpacing': '20', 'rankSpacing': '20' },
'themeVariables': {
'fontSize': '11px',
'fontFamily': 'Arial'
}
}}%%
flowchart TD
jsonTableSpec_start((START))
jsonTableSpec_start --> jsonTableSpec_0_0["JSONTABLE("]:::quoted
jsonTableSpec_0_0 --> jsonTableSpec_0_2[<a href="Invantive UniversalSQL/Grammar/Expression" class="internal-link">expression</a>]
jsonTableSpec_0_2 --> jsonTableSpec_0_3[null]
jsonTableSpec_0_3 --> jsonTableSpec_0_4[jsonTablePassing]
jsonTableSpec_0_4 --> jsonTableSpec_0_5[jsonTableLiteral]
jsonTableSpec_0_5 --> jsonTableSpec_0_6[jsonTableColumns]
jsonTableSpec_0_6 --> jsonTableSpec_0_7[")"]:::quoted
jsonTableSpec_0_7 --> jsonTableSpec_end((END))
```
## ndjsonTableSpec
```mermaid
%%{init: {
'theme': 'base',
'flowchart': { 'padding': '7', 'nodeSpacing': '20', 'rankSpacing': '20' },
'themeVariables': {
'fontSize': '11px',
'fontFamily': 'Arial'
}
}}%%
flowchart TD
ndjsonTableSpec_start((START))
ndjsonTableSpec_start --> ndjsonTableSpec_0_0[NDJSONTABLE]:::quoted
ndjsonTableSpec_0_0 --> ndjsonTableSpec_0_1["("]:::quoted
ndjsonTableSpec_0_1 --> ndjsonTableSpec_0_2[<a href="Invantive UniversalSQL/Grammar/Expression" class="internal-link">expression</a>]
ndjsonTableSpec_0_2 --> ndjsonTableSpec_0_3[null]
ndjsonTableSpec_0_3 --> ndjsonTableSpec_0_4[jsonTablePassing]
ndjsonTableSpec_0_4 --> ndjsonTableSpec_0_5[jsonTableLiteral]
ndjsonTableSpec_0_5 --> ndjsonTableSpec_0_6[IGNORE]:::quoted
ndjsonTableSpec_0_6 --> ndjsonTableSpec_0_7[INVALID]:::quoted
ndjsonTableSpec_0_7 --> ndjsonTableSpec_0_8[LINE]:::quoted
ndjsonTableSpec_0_8 --> ndjsonTableSpec_0_9[jsonTableColumns]
ndjsonTableSpec_0_9 --> ndjsonTableSpec_0_10[")"]:::quoted
ndjsonTableSpec_0_10 --> ndjsonTableSpec_end((END))
```
## jsonTablePassing
```mermaid
%%{init: {
'theme': 'base',
'flowchart': { 'padding': '7', 'nodeSpacing': '20', 'rankSpacing': '20' },
'themeVariables': {
'fontSize': '11px',
'fontFamily': 'Arial'
}
}}%%
flowchart LR
Start((START)) --> jsonTablePassing
jsonTablePassing -->| | passingSourceOrPathExpression
passingSourceOrPathExpression --> End((END))
```
## jsonTableLiteral
```mermaid
%%{init: {
'theme': 'base',
'flowchart': { 'padding': '7', 'nodeSpacing': '20', 'rankSpacing': '20' },
'themeVariables': {
'fontSize': '11px',
'fontFamily': 'Arial'
}
}}%%
flowchart LR
jsonTableLiteral_start((START))
jsonTableLiteral_start --> jsonTableLiteral_0_0[LITERAL]:::quoted
jsonTableLiteral_0_0 --> jsonTableLiteral_0_1[<a href="Invantive UniversalSQL/Grammar/Expression" class="internal-link">expression</a>]
jsonTableLiteral_0_1 --> jsonTableLiteral_end((END))
```
## Purpose
A literal value containing a valid JSON document.
## jsonTableColumns
```mermaid
%%{init: {
'theme': 'base',
'flowchart': { 'padding': '7', 'nodeSpacing': '20', 'rankSpacing': '20' },
'themeVariables': {
'fontSize': '11px',
'fontFamily': 'Arial'
}
}}%%
flowchart TD
jsonTableColumns_start((START))
jsonTableColumns_start --> jsonTableColumns_0_0[COLUMNS]:::quoted
jsonTableColumns_0_0 --> jsonTableColumns_0_1[jsonTableColumnSpec]
jsonTableColumns_0_1 --> jsonTableColumns_0_2[","]:::quoted
jsonTableColumns_0_2 --> jsonTableColumns_0_3[jsonTableColumnSpec]
jsonTableColumns_0_3 --> jsonTableColumns_end((END))
```
## Purpose
A list of JSON table column specifications.
## jsonTableColumnSpec
```mermaid
%%{init: {
'theme': 'base',
'flowchart': { 'padding': '7', 'nodeSpacing': '20', 'rankSpacing': '20' },
'themeVariables': {
'fontSize': '11px',
'fontFamily': 'Arial'
}
}}%%
flowchart TD
jsonTableColumnSpec_start((START))
jsonTableColumnSpec_start --> jsonTableColumnSpec_0_0[<a href="Invantive UniversalSQL/Grammar/Identifier" class="internal-link">identifier</a>]
jsonTableColumnSpec_0_0 --> jsonTableColumnSpec_0_1[sqlDataTypeExtended]
jsonTableColumnSpec_0_1 --> jsonTableColumnSpec_0_2[PATH]:::quoted
jsonTableColumnSpec_0_2 --> jsonTableColumnSpec_0_3[stringConstant]
jsonTableColumnSpec_0_3 --> jsonTableColumnSpec_end((END))
```
## Purpose
The columns are specified using their JSON path relative to the master path.