SQL Reference - JSON

JSON_SCALAR

JSON_SCALAR is a SQL function, specifically tailored for JSON data extraction. It is employed to retrieve a scalar value from a JSON string. The scalar value fetched could be a single number, string, or Boolean value, residing at a defined path within the JSON structure. Only a scalar value, and not an object or an array, can be fetched with this function. For complex JSON data, other JSON specific functions may be more suitable.

Example

SELECT JSON_SCALAR('{"id": 2, "name": "Alice"}', '$.name') AS result;

Output

+--------+
| result |
+--------+
| Alice  |
+--------+

Explanation

The JSON_SCALAR function in the given SQL statement extracts the value associated with the key ‘name’ from the provided JSON object. The output of the function, in this scenario, is ‘Alice’.

Example

SELECT jsonb_build_object('key', jsonb_scalar('value')) AS result;

Output

  result
----------
 {"key": "value"}

Explanation

The jsonb_build_object function in the above example is used to create a JSON object. Inside this function, jsonb_scalar is used to create a JSON scalar value ‘value’. The result is a JSON object with the key ‘key’ and the value ‘value’.