JSON_VALUE

Syntax

VARCHAR JSON_VALUE(VARCHAR content, VARCHAR path)

Input parameters

• content

  The JSON object that you want to parse, which is represented as a string. This parameter is of the VARCHAR type.

• path
  The path expression that is used to parse the JSON object. This parameter is of the VARCHAR type. The following table describes the path expressions that are supported by the JSON_VALUE function.

  Symbol	Description
  $	The root object.
  []	The array subscript.
  *	The array wildcard.
  .	The child element.

Description

Extracts the value of the specific path from a JSON string. If the JSON string is invalid or an input parameter is null, null is returned.

Example

• Test data

  id(INT)	json(VARCHAR)	path1(VARCHAR)
  1	[10, 20, [30, 40]]	$[2][*]
  2	null	$.ccc.hhh[*]
  3	{"aaa":"bbb","ccc":{"ddd":"eee","fff":"ggg","hhh":["h0","h1","h2"]},"iii":"jjj"}	$.ccc.hhh[1]
  4	[10, 20, [30, 40]]	NULL
  5	NULL	$[2][*]
  6	"{xx]"	"$[2][*]"

• Test statement

  SELECT 
      id,
      JSON_VALUE(json, path1) AS `value`
  FROM 
      T1;

• Test results

  id (INT)	value (VARCHAR)
  1	[30,40]
  2	["h0","h1","h2"]
  3	h1
  4	NULL
  5	NULL
  6	NULL