Collections:
JSON_EXTRACT() - Exacting Members from JSON
How to extract child members from a JSON (JavaScript Object Notation) value using the JSON_EXTRACT() function?
✍: FYIcenter.com
JSON_EXTRACT(json, path1, path2, ...) is a MySQL built-in function that extracts one or more child members from a JSON value at given locations. Each path argument specifies a child member location patten in the form of "$", "$.key", "$[i]", "$.*" or "$[*]".
JSON_EXTRACT() returns a MySQL null or a JSON value, depending on the following conditions:
For example:
SET @j = '{"a":null, "b":[1,[2,[3,4]]], "c":{"x":6,"y":[7,[8,9]]}}'; SELECT JSON_EXTRACT(@j, '$.a'), JSON_EXTRACT(@j, '$.e'); -- +-------------------------+-------------------------+ -- | JSON_EXTRACT(@j, '$.a') | JSON_EXTRACT(@j, '$.e') | -- +-------------------------+-------------------------+ -- | null | NULL | -- +-------------------------+-------------------------+ SELECT JSON_EXTRACT(@j, '$.*'); -- +------------------------------------------------------+ -- | JSON_EXTRACT(@j, '$.*') | -- +------------------------------------------------------+ -- | [null, [1, [2, [3, 4]]], {"x": 6, "y": [7, [8, 9]]}] | -- +------------------------------------------------------+ SELECT JSON_EXTRACT(@j, '$.b[1]', '$.c.y[*]'); -- +----------------------------------------+ -- | JSON_EXTRACT(@j, '$.b[1]', '$.c.y[*]') | -- +----------------------------------------+ -- | [[2, [3, 4]], 7, [8, 9]] | -- +----------------------------------------+
Reference information of the JSON_EXTRACT() function:
JSON_EXTRACT(json, path1, path2, ...): val Extracts one or more child members from a JSON value at given locations. Each path argument specifies a child member location patten in the form of "$", "$.key", "$[i]", "$.*" or "$[*]". Arguments, return value and availability: json: Required. The JSON value to be extracted from. path1, path2, ...: One or more JSON paths to search for. val: Return value. The extracted result. Available since MySQL 5.7.
Related MySQL functions:
⇒ JSON_STORAGE_FREE() - Free Space in JSON Value
⇐ JSON_DEPTH() - Calculating Depth of JSON Value
2023-12-11, 262🔥, 0💬
Popular Posts:
What Is "mysqld" in MySQL? "mysqld" is MySQL server daemon program which runs quietly in background ...
How To Convert Numeric Expression Data Types using the CAST() Function in SQL Server Transact-SQL? I...
What Is a Parameter File in Oracle? A parameter file is a file that contains a list of initializatio...
How to run Queries with SQL Server Management Studio Express in SQL Server? 1. Launch and connect SQ...
Where to find MySQL database server tutorials? Here is a collection of tutorials, tips and FAQs for ...