How to extract child members from a JSON (JavaScript Object Notation) value using the JSON_EXTRACT() function?

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:

• Returns a MySQL null, if not match found.
• Returns the member at the matched location as a JSON value, if one match found.
• Returns all members at matched locations as a JSON array, if multiple match found.

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_EXTRACT() - Exacting Members from JSON
• JSON_VALUE() - Converting JSON Value

⇒ JSON_STORAGE_FREE() - Free Space in JSON Value

⇐ JSON_DEPTH() - Calculating Depth of JSON Value

⇑ MySQL Functions on JSON Values

⇑⇑ MySQL Function References