"JavaScript Object Notation", popularly known as JSON is a data type in Postgres for storing data in “key-value” pairs format. The JSON can hold/store data of any valid data type, such as INT, TEXT, Boolean, etc. Therefore, while working with JSON data, determining the data type is a crucial task that helps us avoid data ambiguity. For this purpose, the JSON_TYPEOF() function is used in Postgres.

This write-up will guide you on using the JSON_TYPEOF() function in PostgreSQL using practical examples.

How to Get the Type of a JSON Value in PostgreSQL?

JSON_TYPEOF() is a built-in JSON function that accepts any valid SQL value and retrieves its data type in text format.

Syntax

Follow the below-provided syntax to implement JSON_TYPEOF() function in PostgreSQL:

JSON_TYPEOF(value);

Replace “value” with any valid SQL value of your choice.

Example 1: Using JSON_TYPEOF() on SQL Values

The following example illustrates the basic usage of the JSON_TYPEOF() function in PostgreSQL:

SELECT
JSON_TYPEOF('"Commandprompt"'),
JSON_TYPEOF('true'),
JSON_TYPEOF('false'),
JSON_TYPEOF('121415'),
JSON_TYPEOF('1214.15'),
JSON_TYPEOF('"121415"'),
JSON_TYPEOF('[11, 21,31]'),
JSON_TYPEOF('{"num":100}'),
JSON_TYPEOF('null');

The above piece of code will retrieve the following output:

The output confirms the JSON_TYPEOF() function successfully retrieves the data type of each value passed to it as an argument.

Example 2: Using JSON_TYPEOF() on NULL Values

Consider the below-provided code to learn how the JSON_TYPEOF() deals with the NULL values:

SELECT JSON_TYPEOF(NULL);

The output snippet shows that passing a NULL value to the JSON_TYPEOF() retrieves “null” in the output:

Alternatively, you can verify the return value of the NULL value using the “IS NULL” operator, as follows:

SELECT json_typeof(NULL) IS NULL;

The “True” value in the output shows that the type of the NULL value is “null”:

That’s all about using the JSON_TYPOF() function in PostgreSQL.

Conclusion

JSON_TYPEOF() is a built-in JSON function that accepts any valid SQL value and retrieves its data type in text format. Use the “JSON_TYPEOF(value);” syntax to get the type of a JSON object. Replace “value” with any valid SQL value of your choice. This post has demonstrated a practical guide on different use cases of the JSON_TYPEOF() function in Postgres.