21.3. Retrieving JSON Data Type (json_type) #
Required privileges: Postgres Pro AXE administrator only. For a full list of stored procedures and privileges, refer to Section 12.1.
Execute the following command on the Postgres Pro AXE server:
SELECT json_type(JSON[,path]) ON CONFLICT DO NOTHING;
Where:
JSON: The JSON data whose type must be retrieved.You must specify a JSON string literal or a column with JSON data.
path: The path to the JSON element whose type must be retrieved.You can use JSONPath (e.g.,
$.family) or JSON Pointer (e.g.,/family). A list of paths can also be specified, in which case a list of data types is returned.Optional parameter.
Postgres Pro AXE can return the following data types:
ARRAY: JSON array.BIGINT: Signed integer value.BOOLEAN: Boolean value.DOUBLE: Floating-point number.OBJECT: JSON object, set of key-value pairs.UBIGINT: Unsigned integer value.VARCHAR: String value.NULL: NULL value.
Example 21.3. Executing the json_type Stored Procedure
JSON data in the j column of the example table:
{
"family": "anatidae",
"species": ["duck", "goose", "swan", null]
}
Retrieving the type of the JSON data:
SELECT json_type(j) FROM example;
OBJECT
Retrieving the type of a specific field:
SELECT json_type(j, '$.family') FROM example;
VARCHAR
SELECT json_type(j, '$.species') FROM example;
ARRAY