Rechercher dans le manuel MySQL
12.17.6 JSON Table Functions
This section contains information about JSON functions that
convert JSON data to tabular data. In MySQL 8.0.4 and later, one
such function—JSON_TABLE()
—is
supported.
JSON_TABLE(
expr
,path
COLUMNS (column_list
) [AS]alias
)Extracts data from a JSON document and returns it as a relational table having the specified columns. The complete syntax for this function is shown here:
JSON_TABLE( expr, path COLUMNS (column_list) ) [AS] alias column_list: column[, column][, ...] column: name FOR ORDINALITY | name type PATH string path [on_error] [on_empty] | name type EXISTS PATH string path | NESTED [PATH] path COLUMNS (column_list) on_error: {NULL | ERROR | DEFAULT json_string} ON ERROR on_empty: {NULL | ERROR | DEFAULT json_string} ON EMPTY
expr
: This is an expression that returns JSON data. This can be a constant ('{"a":1}'
), a column (t1.json_data
, given tablet1
specified prior toJSON_TABLE()
in theFROM
clause), or a function call (JSON_EXTRACT(t1,jsn_data,'$.post.comments')
).path
: A JSON path expression, which is applied to the data source. We refer to the JSON value matching the path as the row source; this is used to generate a row of relational data. TheCOLUMNS
clause evaluates the row source, finds specific JSON values within the row source, and returns those JSON values as SQL values in individual columns of a row of relational data.The
alias
is required. The usual rules for table aliases apply (see Section 9.2, “Schema Object Names”).JSON_TABLE()
supports four types of columns, described in the following list:
: This type enumerates rows in thename
FOR ORDINALITYCOLUMNS
clause; the column namedname
is a counter whose type isUNSIGNED INT
, and whose initial value is 1. This is equivalent to specifying a column asAUTO_INCREMENT
in aCREATE TABLE
statement, and can be used to distinguish parent rows with the same value for multiple rows generated by aNESTED [PATH]
clause.
: Columns of this type are used to extract values specified byname
type
PATHstring_path
[on_error
] [on_empty
]string_path
.type
is a MySQL data type.JSON_TABLE()
extracts data as JSON then coerces it to the column type, using the regular automatic type conversion applying to JSON data in MySQL. The exact behavior depends on the column type: If the column type is an SQL type, then only a scalar value can be saved in the column. Saving an object or array triggers theon error
clause; this also occurs when an error takes place during coercion from the value saved as JSON to the table column, such as trying to save the string'asd'
to an integer column. A missing value triggers theon_empty
clause.The optional
on_error
clause determines whatJSON_TABLE()
does when saving an object or array:NULL ON ERROR
: The column is set toNULL
; this is the default behavior. If an error occurs during type coercion, a warning is thrown.ERROR ON ERROR
: An error is thrown.DEFAULT
: Thejson string
ON ERRORjson_string
is parsed as JSON (provided that it is valid) and stored instead of the object or array. A warning is thrown if the error is caused by type coercion. Column type rules also apply to the default value.
When a value saved to a column is truncated, such as saving 3.14159 in a
DECIMAL(10,1)
column, a warning is issued independently of anyON ERROR
option. When multiple values are truncated in a single statement, the warning is issued only once.The optional
on empty
clause determines whatJSON_TABLE()
does in the event that data is missing (depending on type). This clause is also triggered on a column in aNESTED PATH
clause when the latter has no match and aNULL
complemented row is produced for it.on empty
takes one of the following values:NULL ON EMPTY
: The column is set toNULL
; this is the default behavior.ERROR ON EMPTY
: An error is thrown.DEFAULT
: the providedjson_string
ON EMPTYjson_string
is parsed as JSON, as long as it is valid, and stored instead of the missing value. Column type rules also apply to the default value.
This query demonstrates the use of the
ON ERROR
andON EMPTY
options. The row corresponding to{"b":1}
is empty for the path"$.a"
, and attempting to save[1,2]
as a scalar produces an error; these rows are highlighted in the output shown.- -> FROM
- -> JSON_TABLE(
- -> '[{"a":"3"},{"a":2},{"b":1},{"a":0},{"a":[1,2]}]',
- -> "$[*]"
- -> rowid FOR ORDINALITY,
- -> )
- +-------+------+------------+------+
- | rowid | ac | aj | bx |
- +-------+------+------------+------+
- | 1 | 3 | "3" | 0 |
- | 2 | 2 | 2 | 0 |
- | 3 | 111 | {"x": 333} | 1 |
- | 4 | 0 | 0 | 0 |
- | 5 | 999 | [1, 2] | 0 |
- +-------+------+------------+------+
: This column returns 1 if any data is present at the location specified byname
type
EXISTS PATHpath
path
, and 0 otherwise.type
can be any valid MySQL data type, but should normally be specified as some variety ofINT
.NESTED [PATH]
: This flattens nested objects or arrays in JSON data into a single row along with the JSON values from the parent object or array. Using multiplepath
COLUMNS (column_list
)PATH
options allows projection of JSON values from multiple levels of nesting into a single row.The
path
is relative to the parent path row path ofJSON_TABLE()
, or the path of the parentNESTED [PATH]
clause in the event of nested paths.
Column names are subject to the usual rules and limitations governing table column names. See Section 9.2, “Schema Object Names”.
All JSON and JSON path expressions are checked for validity; an invalid expression of either type causes an error.
Each match for the
path
preceding theCOLUMNS
keyword maps to an individual row in the result table. For example, the following query gives the result shown here:- -> FROM
- -> JSON_TABLE(
- -> '[{"x":2,"y":"8"},{"x":"3","y":"7"},{"x":"4","y":6}]',
- -> )
- +------+------+
- | xval | yval |
- +------+------+
- | 2 | 8 |
- | 3 | 7 |
- | 4 | 6 |
- +------+------+
The expression
"$[*]"
matches each element of the array. You can filter the rows in the result by modifying the path. For example, using"$[1]"
limits extraction to the second element of the JSON array used as the source, as shown here:- -> FROM
- -> JSON_TABLE(
- -> '[{"x":2,"y":"8"},{"x":"3","y":"7"},{"x":"4","y":6}]',
- -> )
- +------+------+
- | xval | yval |
- +------+------+
- | 3 | 7 |
- +------+------+
Within a column definition,
"$"
passes the entire match to the column;"$.x"
and"$.y"
pass only the values corresponding to the keysx
andy
, respectively, within that match. For more information, see JSON Path Syntax.NESTED PATH
(or simplyNESTED
;PATH
is optional) produces a set of records for each match in theCOLUMNS
clause to which it belongs. If there is no match, all columns of the nested path are set toNULL
. This implements an outer join between the topmost clause andNESTED [PATH]
. An inner join can be emulated by applying a suitable condition in theWHERE
clause, as shown here:- -> FROM
- -> JSON_TABLE(
- -> '[ {"a": 1, "b": [11,111]}, {"a": 2, "b": [22,222]}, {"a":3}]',
- -> )
- +------+------+
- | a | b |
- +------+------+
- | 1 | 11 |
- | 1 | 111 |
- | 2 | 22 |
- | 2 | 222 |
- +------+------+
Sibling nested paths—that is, two or more instances of
NESTED [PATH]
in the sameCOLUMNS
clause—are processed one after another, one at a time. While one nested path is producing records, columns of any sibling nested path expressions are set toNULL
. This means that the total number of records for a single match within a single containingCOLUMNS
clause is the sum and not the product of all records produced byNESTED [PATH]
modifiers, as shown here:- -> FROM
- -> JSON_TABLE(
- -> '[{"a": 1, "b": [11,111]}, {"a": 2, "b": [22,222]}]',
- -> )
- +------+------+------+
- | a | b1 | b2 |
- +------+------+------+
- +------+------+------+
A
FOR ORDINALITY
column enumerates records produced by theCOLUMNS
clause, and can be used to distinguish parent records of a nested path, especially if values in parent records are the same, as can be seen here:- -> FROM
- -> JSON_TABLE(
- -> '[{"a": "a_val",
- '> "b": [{"c": "c_val", "l": [1,2]}]},
- '> {"a": "a_val",
- '> "b": [{"c": "c_val","l": [11]}, {"c": "c_val", "l": [22]}]}]',
- -> '$[*]' COLUMNS(
- -> top_ord FOR ORDINALITY,
- -> apath VARCHAR(10) PATH '$.a',
- -> NESTED PATH '$.b[*]' COLUMNS (
- -> bpath VARCHAR(10) PATH '$.c',
- -> ord FOR ORDINALITY,
- -> NESTED PATH '$.l[*]' COLUMNS (lpath varchar(10) PATH '$')
- -> )
- -> )
- -> ) as jt;
- +---------+---------+---------+------+-------+
- | top_ord | apath | bpath | ord | lpath |
- +---------+---------+---------+------+-------+
- | 1 | a_val | c_val | 1 | 1 |
- | 1 | a_val | c_val | 1 | 2 |
- | 2 | a_val | c_val | 1 | 11 |
- | 2 | a_val | c_val | 2 | 22 |
- +---------+---------+---------+------+-------+
The source document contains an array of two elements; each of these elements produces two rows. The values of
apath
andbpath
are the same over the entire result set; this means that they cannot be used to determine whetherlpath
values came from the same or different parents. The value of theord
column remains the same as the set of records havingtop_ord
equal to 1, so these two values are from a single object. The remaining two values are from different objects, since they have different values in theord
column.
Nederlandse vertaling
U hebt gevraagd om deze site in het Nederlands te bezoeken. Voor nu wordt alleen de interface vertaald, maar nog niet alle inhoud.Als je me wilt helpen met vertalingen, is je bijdrage welkom. Het enige dat u hoeft te doen, is u op de site registreren en mij een bericht sturen waarin u wordt gevraagd om u toe te voegen aan de groep vertalers, zodat u de gewenste pagina's kunt vertalen. Een link onderaan elke vertaalde pagina geeft aan dat u de vertaler bent en heeft een link naar uw profiel.
Bij voorbaat dank.
Document heeft de 26/06/2006 gemaakt, de laatste keer de 26/10/2018 gewijzigd
Bron van het afgedrukte document:https://www.gaudry.be/nl/mysql-rf-json-table-functions.html
De infobrol is een persoonlijke site waarvan de inhoud uitsluitend mijn verantwoordelijkheid is. De tekst is beschikbaar onder CreativeCommons-licentie (BY-NC-SA). Meer info op de gebruiksvoorwaarden en de auteur.
Referenties
Deze verwijzingen en links verwijzen naar documenten die geraadpleegd zijn tijdens het schrijven van deze pagina, of die aanvullende informatie kunnen geven, maar de auteurs van deze bronnen kunnen niet verantwoordelijk worden gehouden voor de inhoud van deze pagina.
De auteur Deze site is als enige verantwoordelijk voor de manier waarop de verschillende concepten, en de vrijheden die met de referentiewerken worden genomen, hier worden gepresenteerd. Vergeet niet dat u meerdere broninformatie moet doorgeven om het risico op fouten te verkleinen.