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, 256🔥, 0💬
Popular Posts:
Where to find answers to frequently asked questions on PHP Connections and Query Execution for MySQL...
How to set database to be READ_ONLY in SQL Server? Databases in SQL Server have two update options: ...
How To Start MySQL Server in MySQL? If you want to start the MySQL server, you can run the "mysqld" ...
How To Drop a Stored Procedure in Oracle? If there is an existing stored procedure and you don't wan...
How To Calculate Age in Days, Hours and Minutes in SQL Server Transact-SQL? On many Web sites, news ...