JSON Functions
IS_JSON
Description
Checks if the string contains valid JSON content.
IS_JSON ( string [, json_type_constraint] )Parameters
-
string- the string to check -
json_type_constraint- the JSON content type to check for; possible values:-
VALUE -
ARRAY -
OBJECT -
SCALAR
-
Example
Check 'years' for JSON objects:
SELECT IS_JSON('{"years":[1999, 2011, 2022]}');JSON_ARRAY
Description
Creates a JSON array from the specified expressions.
JSON_ARRAY ( [ <json_array_value> [,...n] ])Parameters
json_array_value - the value of an element in the JSON array
Example
Create a JSON array out of the elements: 'example', 1, and 4.2:
SELECT JSON_ARRAY('example', 1, 4.2)JSON_MODIFY
Description
Updates the value of a property and returns the updated JSON string.
JSON_MODIFY ( expression , json_path , newValue )Parameters
-
expression- the name of a variable or a column that contains JSON text -
path- JSON path that specifies an object or an array to extract -
newValue- the new value to assign to the specified property
Example
Change Bristol to London in the 'info' JSON string.
//Initial JSON
//{"info":{"type":1,"address":{"town":"Bristol","country":"England"},"tags":["Sport","Water polo"]}}
SELECT JSON_MODIFY(J, '$.info.address.town', 'London') FROM TEST;
//Updated JSON
//{"info":{"type":1,"address":{"town":"London","country":"England"},"tags":["Sport","Water polo"]}}JSON_OBJECT
Description
Creates a JSON object based on the specified expression.
JSON_OBJECT ( [ <json_key_value> [,...n] ])Parameters
json_key_value` - an expression that defines the value of the JSON key: (key1, value1, key2, value2…)
Example
Create a JSON object:
select JSON_OBJECT(SELECT * FROM (select * from VALUES (1), (2), (3), (4), (5), (6), (7)) as t limit 4)JSON_QUERY
Description
Extracts an object or an array from a JSON string.
JSON_QUERY ( expression , path )Parameters
-
expression- the name of a variable or a column that contains JSON text -
path- JSON path that specifies the object or array to extract
Example
Extract the 'info' object:
select JSON_QUERY('{"info":{"address":[{"town":"Paris"},{"town":"London"}]}}','$.info.address')JSON_VALUE
Description
Extracts a scalar value from a JSON string.
JSON_VALUE( expression , path )Parameters
-
expression- the name of a variable or a column that contains JSON text -
path- JSON path that specifies the value to extract
Example
Extract the 'town' value from the 'info' JSON string.
select JSON_VALUE('{"info":{"address":[{"town":"Paris"},{"town":"London"}]}}','$.info.address[0].town')© 2024 GridGain Systems, Inc. All Rights Reserved. Privacy Policy | Legal Notices. GridGain® is a registered trademark of GridGain Systems, Inc.
Apache, Apache Ignite, the Apache feather and the Apache Ignite logo are either registered trademarks or trademarks of The Apache Software Foundation.