JSON_TABLE (SQL)

Synopsis

JSON_TABLE( json-value, json-path col-mapping )

Description

The JSON_TABLE function returns a table that can be used in a SQL query by mapping JSONOpens in a new tab values into columns. Mappings from a JSON value to a column are written as SQL/JSON path language expressions.

As a table-valued function, JSON_TABLE returns a table that can be used in the FROM clause of a SELECT statement to access data stored in a JSON value; this table does not persist across queries. Multiple calls to JSON_TABLE can be made within a single FROM clause and can appear alongside other table-valued functions.

Arguments

json-value

The json-value argument can take one of three forms: a literal JSON string, a function returning a JSON string, or a column containing JSON values. Note that if you are specifying a column, you must list the table that the column is in the FROM clause.

The examples below demonstrate valid json-values:

'[{"month":"November"}, {"month":"May"}, {"month":"January"}]' // a JSON string

%Net.GetJson(<JSON-url>,<SSL-configuration>) // a function that returns a JSON string

Profile.Address // a column that contains JSON string, where the 
                   Profile table is listed in the FROM clause

json-path

The json-path argument is a SQL/JSON path language expression that specifies which values to extract from the json-value. Use '$' to specify the entirety of json-value. This argument is used to filter a JSON document to select a particular subset of JSON objects with similar fields; the individual elements of this subset correspond to the rows in the table returned by JSON_TABLE.

See Using a SQL/JSON Path Language Expression for further explanation of syntax.

Note that the json-path argument is only separated by a space from the col-mapping argument, as per the SQL standard.

col-mapping

The col-mapping argument uses a COLUMNS clause to define the columns of the table that is returned by JSON_TABLE. Each entry in the COLUMNS clause must have a column name, a type, and a SQL/JSON path language expression that identifies the value that the column should contain, beginning from the path identified by json-path.

The json-path argument accesses a path that returns a subset of objects from the JSON document, the JSON path expression in col-mapping should be used to select a specific field of the objects in the subset.

There is no limit to the number of column mappings that you can define.

See Using a SQL/JSON Path Language Expression for further explanation of SQL/JSON path language expression syntax.

Using a SQL/JSON Path Language Expression

A SQL/JSON path language expression is a sequence of operators that define a path through a JSON document to a desired set of values. Within the context of JSON_TABLE, two separate SQL/JSON path language expressions (one from json-path and one from col-mapping) combine to define a single column mapping. The SQL/JSON path language expression in the json-path argument defines the set of objects (which map to rows) that contain the fields you want to map to columns, while the SQL/JSON path language expression in the col-mapping argument defines the specific field you want to map to a column.

The operations you can use are outlined in the following table.

Operator	Description	Examples
$	Selects the entirety of the JSON value and starts all SQL/JSON path language expressions.	$ — selects the entirety of the JSON document
.<fieldname>	Selects field from the JSON object. Use * to specify all fields.	$.number — selects the number field of the JSON object $.* — selects every field of the JSON object
[]	Selects a range of elements from a JSON array. Use * to indicate every element. To specify a range of indexes, use x to y syntax, where integer x is less than integer y. To specify individual indexes, separate them with commas. You can specify both ranges and individual indexes within the same array selector for more complex selections.	$[*] — selects all elements of a JSON array $[0 to 5] — selects the first through sixth elements of a JSON array $[0,3,5] — selects the first, fourth, and sixth elements of a JSON array $[0 to 3, 5] — selects the first through fourth elements, as well as the sixth element
?(<filter-expression>)	Starts a filter operation, where a filter expression is written within the parentheses. Valid conditional operators are &&, \|\|, !, ==, !=, <, <=, >, >=, starts with, and exists().	$[]?(@.number > 0) — selects all elements of a JSON array, then filters through them to select only the elements where the value in the number field is greater than 0 $[]?(@.order starts with "first") — selects all elements of a JSON array, then filters through them to select only the elements where the value in the order field starts with "first"
@	Used within the condition of a filter operation to represent the current item.

Examples

The following example selects the my_value field from mapping of JSON values to columns. The values from these columns are provided by a JSON value, which is an array comprised of three objects that each have a number field. The json-path argument specifies the entire JSON value as the context item that the SQL/JSON path language expression in the col-mapping will match against. The col-mapping SQL/JSON path language expression specifies that the my-value column takes its values from the number field of the objects within the array. The result is a table with one column, named my_value, and three rows.

SELECT my_value FROM JSON_TABLE(
  '[{"number":"two"}, {"number":"three"}, {"number":"four"}]',
  '$'
  COLUMNS ( my_value varchar(20) PATH '$.number' )
)

| my_value |
| -- |
| two |
| three |
| four |

The following example selects the first 10 states and names of current US senators by using %Net.GetJson()Opens in a new tab to provide a JSON value. Note that this example uses an SSL configuration called Default that has previously been defined on the instance of InterSystems IRIS® data platform to access the JSON data; for information about creating an SSL configurations, see Create or Edit a TLS Configuration.

SELECT TOP 10 state, name
  FROM JSON_TABLE(%Net.GetJson('https://www.govtrack.us/api/v2/role?current=true&role_type=senator','{"SSLConfiguration":"Default"}'),
  '$.objects'
    COLUMNS ( name VARCHAR(100) PATH '$.person.name',
              sortname VARCHAR(100) PATH '$.person.sortname',
              state VARCHAR(50) PATH '$.person.state' )
  ) as jt
  ORDER BY state, sort_name

| state | name|
| -- | -- |
| AK | Sen. Lisa Murkowski [R-AK] |
| AK | Sen. Dan Sullivan [R-AK] |
| AL | Sen. Katie Britt [R-AL] |
| AL | Sen. Tommy Tuberville [R-AL] |
| AR | Sen. John Boozman [R-AR] |
| AR | Sen. Tom Cotton [R-AR] |
| AZ | Sen. Mark Kelly [D-AZ] |
| AZ | Sen. Kyrsten Sinema [I-AZ] |
| CA | Sen. Alejandro "Alex PAdilla [D-CA] |
| CO | Sen. John Hickenlooper [D-CO] |

The following example first creates a new table, Senators, and then inserts data from the JSON document accessed in the previous example into the Senators table. The data from the JSON document can then be queried natively through SQL.

CREATE TABLE Senators ( person VARCHAR(100),
                        extra VARCHAR (100),
                        state VARCHAR(2) )

INSERT INTO Senators ( person, extra, state )
  SELECT person, extra, state
    FROM JSON_TABLE(%Net.GetJson('https://www.govtrack.us/api/v2/role?current=true&role_type=senator','{"SSLConfiguration":"Default"}'),
  '$.content.objects'
      COLUMNS ( person VARCHAR(100) PATH '$.person',
                extra VARCHAR(100) PATH '$.extra',
                state VARCHAR(50) PATH '$.state'
      )
    )
)

The following example uses the Senators table created in the previous example in a query that uses lateral joins to select 10 senators, the state they are from, their birth date, and their address. Since the columns in the Senators table contain JSON strings in the person and extra fields, the JSON_TABLE function is used twice to return tables that access the values in those fields. The example employs an implicit lateral join, as the Senators table is queried to provide the state field and referenced within the JSON_TABLE function.

SELECT TOP 10 jtp.name, state, jtp.birth_date, jte.address
  FROM Senators as Sen,
       JSON_TABLE(Sen.person, '$'
         COLUMNS ( name VARCHAR(60) path '$.sortname',
                   birth_date VARCHAR(10) path '$.birthday'
         )
       ) as jtp,
       JSON_TABLE(Sen.extra, '$'
         COLUMNS ( address VARCHAR(100) path '$.address' )
       ) as jte

| state | name | birth_date | address |
| -- | -- | -- | -- |
| WA | Cantwell, Maria (Sen.) [D-WA] | 1958-10-13 | 511 Hart Senate Office Building Washington DC 20510 |
| DE | Carper, Thomas (Sen.) [D-DE] | 1947-01-23 | 513 Hart Senate Office Building Washington DC 20510 |
| CA | Padilla, Alejandro “Alex” (Sen.) [D-CA] | 1973-03-22 | 112 Hart Senate Office Building Washington DC 20510 |
| MI | Stabenow, Debbie (Sen.) [D-MI] | 1950-04-29 | 731 Hart Senate OFfice Building |
| WI | Baldwin, Tammy (Sen.) [D-WI] | 1962-02-11 | 709 Hart Senate Office Building Washington DC 20510 |
| TN | Blackburn, Marsha (Sen.) [R-TN] | 1952-06-06 | 357 Dirksen Senate Office Building Washington DC 20510 |
| OH | Brown, Sherrod (Sen.) [D-OH] | 1952-11-09 | 503 Hart Senate Office Building Washington DC 20510 |
| MD | Cardin, Benjamin (Sen.) [D-MD] | 1943-10-05 | 509 Hart Senate Office Building Washington DC 20510 |
| NJ | Menendez, Robert “Bob” (Sen.) [D-NJ] | 1954-01-01 | 528 Hart Senate Office Building Washington DC 20510 |
| VT | Sanders, Bernard “Bernie” (Sen.) [I-VT] | 1941-09-08 | 332 Dirksen Senate Office Building Washington DC 20510 |

The example below creates a table, Example.JsonValues, and inserts a JSON value into it. This table is then queried using JSON_TABLE to return the fields of the JSON value as separate rows.

CREATE TABLE Example.JsonValues (numbers VARCHAR(200))

INSERT INTO Example.JsonValues (numbers) VALUES ('[{"number":0},{"number":1},{"number":2}]')

SELECT number FROM Example.JsonValues, JSON_TABLE(JsonValues.numbers, '$' 
  COLUMNS (
    number INTEGER PATH '$.number'
  )
)

| number |
| -- |
| 0 |
| 1 |
| 2 |

In the following example, the Example.JsonValues table is queried without using JSON_TABLE

SELECT numbers FROM Example.JsonValues

| numbers |
| -- |
| [{"number":0},{"number":1},{"number":2}] |

The following example queries the Example.JsonValues table to return both the values parsed by JSON_TABLE and the basic string itself.

SELECT * FROM Example.JsonValues, JSON_TABLE(JsonValues, '$' 
  COLUMNS (
    number INTEGER PATH '$.number'
  )
)

| numbers | number |
| -- | -- |
| [{"number":0},{"number":1},{"number":2}] | 0 |
| [{"number":0},{"number":1},{"number":2}] | 1 |
| [{"number":0},{"number":1},{"number":2}] | 2 |