The following methods, functions, procedures, and operators allow you to perform common operations on the JSON type to access or manipulate JSON data.
Methods
- AsBSON
- Returns the BSON representation of the specified JSON instance.
- AsJSONText
- Returns the text representation of the specified JSON instance.
- Combine
- Takes two JSON documents and combines them into a JSON document structured as an array or a JSON document structured as an object.
- ExistValue
- Allows you to specify a name or path in JSONPath syntax to determine if that name or path exists in the JSON document.
- JSONExtract
- Extracts data from a JSON instance. The desired data is specified in a JSONPath expression. The result is a JSON array composed of the values found, or NULL if there are no matches.
- JSONExtractValue
- Allows you to retrieve the text representation of the value of an entity in a JSON instance, specified using JSONPath syntax.
- JSONExtractLargeValue
- Functions the same as JSONExtractValue, except for the return size and type. For LOB-based JSON objects, this method returns a CLOB of 16776192 characters for CHARACTER SET LATIN or 8388096 characters for CHARACTER SET UNICODE.
- KeyCount
- Returns the number of keys in a JSON document.
- Metadata
- Returns metadata about a JSON document such as the number of values that are Objects, Arrays, Strings, or Numbers.
- StorageSize
- Returns the number of bytes needed to store the input JSON data in the specified storage format.
Functions
- ARRAY_TO_JSON
- Allows any Vantage ARRAY type to be converted to a JSON type composed of an array.
- BSON_CHECK
- Checks a string for valid BSON syntax and provides an informative error message about the cause of the syntax failure if the string is invalid.
- DataSize
- Returns the data length in bytes of a JSON instance.
- GeoJSONFromGeom
- Converts an ST_Geometry object into a JSON document that conforms to the GeoJSON standards.
- GeomFromGeoJSON
- Converts a JSON document that conforms to the GeoJSON standards into an ST_Geometry object.
- JSON_AGG
- This aggregate function takes a variable number of input parameters and packages them into a JSON document.
- JSON_COMPOSE
- This scalar function takes a variable number of input parameters and packages them into a JSON document. This function provides a complex composition of a JSON document when used in conjunction with the JSON_AGG function.
- JSON_CHECK
- Checks a string for valid JSON syntax and provides an informative error message about the cause of the syntax failure if the string is invalid.
- JSONGETVALUE
- Extracts a value from a JSON object as a specific type.
- JSONMETADATA
- An aggregate function that returns metadata about a set of JSON documents.
- NVP2JSON
- Converts a string of name-value pairs to a JSON object.
Table Operators
- JSON_KEYS
- Parses a JSON instance, from either CHAR or VARCHAR input and returns a list of key names.
- JSON_PUBLISH
- Compose a JSON data type instance or instances from a variety of data sources, that is anything that can be referenced in an SQL statement. It can publish JSON data types of any storage format.
- JSON_TABLE
- Creates a temporary table based on all, or a subset, of the data in a JSON object.
SQL Stored Procedures
- JSON_SHRED_BATCH and JSON_SHRED_BATCH_U
- Allows you to extract values from JSON documents and use the extracted data to populate existing relational tables. This provides a flexible form of loading data from JSON format into the relational model.