Skip to main content

JSON_TABLE (SQL)

Synopsis

InterSystems IRIS® SQL

InterSystems IRIS Cloud Document Service

JSON_TABLE( collection-name FORMAT COLLECTION, 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

collection-name

With InterSystems IRIS Cloud DocumentOpens in a new tab, the JSON_TABLE function is used in conjunction with collections. A collection contains JSON data from one or more JSON documents. After uploading one or more JSON document to your deployment and adding them to collection, you can specify JSON_TABLE to use that collection as the source.

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

InterSystems IRIS SQL

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"}'),
  '$.content.objects'
    COLUMNS ( name VARCHAR(100) PATH '$.person.name',
              sortname VARCHAR(100) PATH '$.person.sortname',
              state VARCHAR(50) PATH '$.state' )
  ) as jt
  ORDER BY state, sortname
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

InterSystems IRIS Cloud Document Service

The following example performs a join across two collections, NBATeams and NBAPlayers, selecting the first and last names of NBA players that play for the Boston Celtics. These collections have been created by adding JSON files to the deployment on the Add and Manage Files page in the Cloud Portal and then importing the JSON data contained in the files into a collection on the Collection Import page.

SELECT firstName, lastName
  FROM JSON_TABLE( NBATeams FORMAT COLLECTION, '$'
                   COLUMNS ( teamId BIGINT path '$.teamId',
                             teamName VARCHAR(50) path '$.teamName' )
                  ) AS Teams
  JOIN JSON_TABLE( NBAPlayers FORMAT COLLECTION, '$'
                   COLUMNS ( teamId BIGINT path '$.teamId',
                             firstName VARCHAR(50) path '$.firstName',
                             lastName VARCHAR(50) path '$.lastName' )
                 ) AS Players
  ON teams.teamId = players.teamId
  WHERE teams.teamName = 'Boston Celtics'
  ORDER BY lastName, firstName

See Also

FeedbackOpens in a new tab