Syntax\n------\n\nJSON_TABLE(json_doc, \n          context_path COLUMNS (column_list)\n) [AS] alias\n\nDescription\n-----------\n\nJSON_TABLE can be used in contexts where a table reference can be used; in the FROM clause of a SELECT statement, and in multi-table UPDATE/DELETE statements.\n\njson_doc is the JSON document to extract data from. In the simplest case, it is a string literal containing JSON. In more complex cases it can be an arbitrary expression returning JSON. The expression may have references to columns of other tables. However, one can only refer to tables that precede this JSON_TABLE invocation. For RIGHT JOIN, it is assumed that its outer side precedes the inner. All tables in outer selects are also considered preceding.\n\ncontext_path is a JSON Path expression pointing to a collection of nodes in json_doc that will be used as the source of rows.\n\nThe COLUMNS clause declares the names and types of the columns that JSON_TABLE returns, as well as how the values of the columns are produced.\n\nColumn Definitions\n\nThe following types of columns are supported:\n\nPath Columns\n\n``sql\nname type PATH path_str [on_empty] [on_error]\n`\n\nLocates the JSON node pointed to by path_str and returns its value. The path_str is evaluated using the current row source node as the context node.\n\n`sql\nSET @json='\n[\n  {"name":"Laptop", "color":"black", "price":"1000"},\n  {"name":"Jeans",  "color":"blue"}\n]';\n\nSELECT  FROM json_table(@json, '$[]' \n  COLUMNS(\n   name  VARCHAR(10) path '$.name', \n   color VARCHAR(10) path '$.color',\n   price DECIMAL(8,2) path '$.price' ) \n) AS jt;\n+--------+-------+---------+\n| name   | color | price   |\n+--------+-------+---------+\n| Laptop | black | 1000.00 |\n| Jeans  | blue  |    NULL |\n+--------+-------+---------+\n`\n\nThe on_empty and on_error clauses specify the actions to be performed when the value was not found or there was an error condition. See the ON EMPTY and ON ERROR clauses section for details.\n\nORDINALITY Columns\n\n`sql\nname FOR ORDINALITY\n`\n\nCounts the rows, starting from 1.\n\nExample:\n\n`sql\nset @json='\n[\n  {"name":"Laptop", "color":"black"},\n  {"name":"Jeans",  "color":"blue"}\n]';\n\nselect  from json_table(@json, '$[]' \n  columns(\n   id for ordinality, \n   name  varchar(10) path '$.name')\n) as jt;\n+------+--------+\n| id   | name   |\n+------+--------+\n|    1 | Laptop |\n|    2 | Jeans  |\n+------+--------+\n`\n\nEXISTS PATH Columns\n\n`sql\nname type EXISTS PATH path_str\n`\n\nChecks whether the node pointed to by value_path exists. The value_path is evaluated using the current row source node as the context node.\n\n`sql\nset @json='\n[\n  {"name":"Laptop", "color":"black", "price":1000},\n  {"name":"Jeans",  "color":"blue"}\n]';\n\nselect  from json_table(@json, '$[]' \n  columns(\n   name  varchar(10) path '$.name',\n   has_price integer exists path '$.price')\n) as jt;\n+--------+-----------+\n| name   | has_price |\n+--------+-----------+\n| Laptop |         1 |\n| Jeans  |         0 |\n+--------+-----------+\n`\n\nNESTED PATHs\n\nNESTED PATH converts nested JSON structures into multiple rows.\n\n`sql\nNESTED PATH path COLUMNS (column_list)\n`\n\nIt finds the sequence of JSON nodes pointed to by path and uses it to produce rows. For each found node, a row is generated with column values as specified by the NESTED PATH's COLUMNS clause. If path finds no nodes, only one row is generated with all columns having NULL values.\n\nFor example, consider a JSON document that contains an array of items, and each item, in turn, is expected to have an array of its available sizes:\n\n`json\nSET @json='\n[\n  {"name":"Jeans",  "sizes": [32, 34, 36]},\n  {"name":"T-Shirt", "sizes":["Medium", "Large"]},\n  {"name":"Cellphone"}\n]';\n`\n\nNESTED PATH allows one to produce a separate row for each size each item has:\n\n`sql\nselect  from json_table(@json, '$[]' \n  columns(\n    name  varchar(10) path '$.name', \n    nested path '$.sizes[]' columns (\n      size varchar(32) path '$'\n    )\n  )\n) as jt;\n+-----------+--------+\n| name      | size   |\n+-----------+--------+\n| Jeans     | 32     |\n| Jeans     | 34     |\n| Jeans     | 36     |\n| T-Shirt   | Medium |\n| T-Shirt   | Large  |\n| Cellphone | NULL   |\n+-----------+--------+\n`\n\nNESTED PATH clauses can be nested within one another. They can also be located next to each other. In that case, the nested path clauses will produce records one at a time. The ones that are not producing records will have all columns set to NULL.\n\nExample:\n\n`sql\nset @json='\n[\n  {"name":"Jeans",  "sizes": [32, 34, 36], "colors":["black", "blue"]}\n]';\n\nselect  from json_table(@json, '$[]' \n  columns(\n    name  varchar(10) path '$.name', \n    nested path '$.sizes[]' columns (\n      size varchar(32) path '$'\n    ),\n    nested path '$.colors[]' columns (\n      color varchar(32) path '$'\n    )\n  )\n) as jt;\n\n+-------+------+-------+\n| name  | size | color |\n+-------+------+-------+\n| Jeans | 32   | NULL  |\n| Jeans | 34   | NULL  |\n| Jeans | 36   | NULL  |\n| Jeans | NULL | black |\n| Jeans | NULL | blue  |\n+-------+------+-------+\n`\n\nON EMPTY and ON ERROR Clauses\n\nThe ON EMPTY clause specifies what will be done when the element specified by the search path is missing in the JSON document.\n\n`sql\non_empty:\n    {NULL | DEFAULT string | ERROR} ON EMPTY\n`\n\nWhen ON EMPTY clause is not present, NULL ON EMPTY is implied.\n\n`sql\non_error:\n    {NULL | DEFAULT string | ERROR} ON ERROR\n`\n\nThe ON ERROR clause specifies what should be done if a JSON structure error occurs when trying to extract the value pointed to by the path expression. A JSON structure error here occurs only when one attempts to convert a JSON non-scalar (array or object) into a scalar value. When the ON ERROR clause is not present, NULL ON ERROR is implied.\n\nNote: A datatype conversion error (e.g. attempt to store a non-integer value into an integer field, or a varchar column being truncated) is not considered a JSON error and so will not trigger the ON ERROR behavior. It will produce warnings, in the same way as CAST(value AS datatype) would.\n\nReplication\n\nIn the current code, evaluation of JSON_TABLE is deterministic, that is, for a given input string JSON_TABLE will always produce the same set of rows in the same order. However, one can think of JSON documents that one can consider identical which will produce different output. In order to be future-proof and withstand changes like\n\n sorting JSON object members by name (like MySQL does);\n changing the way duplicate object members are handled the function is marked as unsafe for statement-based replication.\n\nExtracting a Subdocument into a Column\n\n`sql\nSELECT  FROM JSON_TABLE('{"foo": [1,2,3,4]}','$' columns( jscol json path '$.foo') ) AS T;\n+-----------+\n| jscol     |\n+-----------+\n| [1,2,3,4] |\n+-----------+\n`\n\nJSON_TABLE does not allow to extract a JSON "subdocument" into a JSON column.\n\n`sql\nSELECT * FROM JSON_TABLE('{"foo": [1,2,3,4]}','$' columns( jscol json path '$.foo') ) AS T;\n+-------+\n| jscol |\n+-------+\n| NULL  |\n+-------+\n``\n\nURL: https://mariadb.com/docs/server/reference/sql-functions/special-functions/json-functions/json_table