Differences between JSON_QUERY and JSON_VALUE

The primary difference between the two functions is that JSON_QUERY returns an object or an array, while JSON_VALUE returns a scalar.

Take the following JSON document as an example:

SET @json='{ "x": [0,1], "y": "[0,1]", "z": "Monty" }';

Note that data member "x" is an array, and data members "y" and "z" are strings. The following examples demonstrate the differences between the two functions.

SELECT JSON_QUERY(@json,'$'), JSON_VALUE(@json,'$'); +--------------------------------------------+-----------------------+ | JSON_QUERY(@json,'$') | JSON_VALUE(@json,'$') | +--------------------------------------------+-----------------------+ | { "x": [0,1], "y": "[0,1]", "z": "Monty" } | NULL | +--------------------------------------------+-----------------------+  SELECT JSON_QUERY(@json,'$.x'), JSON_VALUE(@json,'$.x'); +-------------------------+-------------------------+ | JSON_QUERY(@json,'$.x') | JSON_VALUE(@json,'$.x') | +-------------------------+-------------------------+ | [0,1] | NULL | +-------------------------+-------------------------+  SELECT JSON_QUERY(@json,'$.y'), JSON_VALUE(@json,'$.y'); +-------------------------+-------------------------+ | JSON_QUERY(@json,'$.y') | JSON_VALUE(@json,'$.y') | +-------------------------+-------------------------+ | NULL | [0,1] | +-------------------------+-------------------------+  SELECT JSON_QUERY(@json,'$.z'), JSON_VALUE(@json,'$.z'); +-------------------------+-------------------------+ | JSON_QUERY(@json,'$.z') | JSON_VALUE(@json,'$.z') | +-------------------------+-------------------------+ | NULL | Monty | +-------------------------+-------------------------+  SELECT JSON_QUERY(@json,'$.x[0]'), JSON_VALUE(@json,'$.x[0]'); +----------------------------+----------------------------+ | JSON_QUERY(@json,'$.x[0]') | JSON_VALUE(@json,'$.x[0]') | +----------------------------+----------------------------+ | NULL | 0 | +----------------------------+----------------------------+

_{This page is licensed: CC BY-SA / Gnu FDL}