There are several JSON functions that PostgreSQL offers that are applicable to the JSON and JSONB data types. The JSONB data type is the binary form/version of the JSON data type. This came to assist the JSON in analyzing and processing huge amounts of textual data. Some of the JSON functions, among them, are used to manipulate and perform operations on the JSON and JSONB data and some are used to get some information from the given Postgres JSON or JSONB data.

This tutorial will make us:

● Understand the PostgreSQL json_typeof() function.

● Understand the PostgreSQL jsonb_typeof() function.

We will go over them one by one.

Understanding PostgreSQL json_typeof() Function

The json_typeof() function takes a JSON value and returns its type as a string. The basic syntax of json_typeof() function is given as:

json_typeof(json_val JSON)

In the above syntax:

● The json_typeof() function takes a JSON value as input.

● json_typeof() function gives the data type of the provided JSON value as a string/text.

● If the function is provided NULL, the NULL value will be returned.

We can understand the workings of the json_typeof() function using some examples.

Example 1: Understanding the json_typeof() function in PostgreSQL

To learn, how the json_typeof() function works, we can execute the following query:

SELECT
  json_typeof('"Command Prompt"') AS "type of Command Prompt",
  json_typeof('true') AS "type of true",
  json_typeof('[0,7,5,3]') AS "type of [0,7,5,3]",
  json_typeof('973.8383') AS "type of 973.8383",
  json_typeof('{"value":6}') AS "type of Command Prompt {""value"":6}",
  json_typeof('false') AS "type of false",
  json_typeof('null') AS "type of null";

In the above query, we have specified different types of JSON values in the json_typeof() function. The function will return the data types of all the JSON values like this:

It is clear in the above output that the json_typeof() function has returned the data type of the provided JSON values in the TEXT data type.

Example 2: Understanding the json_typeof() function With NULL

When NULL is provided in the quotes, the NULL is returned as a string. This can also be confirmed by the IS NULL statement. The “IS NULL” statement returns true if the expression gives NULL. The following can be the query written in this regard.

SELECT json_typeof('null') IS NULL AS "json type of null";

The output of this query is:

The IS NULL statement returned false because passing the ‘null’ to the json_typeof() function will give the string NULL.

However, if we simply provide the NULL to the json_typeof() function, we will get the NULL value, not the string NULL, like this.

SELECT json_typeof(NULL)"json type of NULL";

The output of this will be:

Now if we apply the IS NULL statement on it, this will return true because the value for this case is NULL.

SELECT json_typeof(NULL) IS NULL AS "json type of null";

Executing this query will give:

The query returned “true”, which means that the json_typeof() function when applied on NULL gives null.

Example 3: Understanding the json_typeof() function in PostgreSQL

The numeric JSON values that are to be provided to the json_typeof() function should be enclosed in the single quotes to get its data type using the json_typeof() function. The second thing we can do to the numeric type JSON values is cast the provided value to the TEXT first and then to the JSON data type. The same is the case with boolean values as illustrated in the below query.

SELECT 
  json_typeof(46858::text::json) AS "json type of 46858",
  json_typeof(8494.453::text::json) AS "json type of 973.8383",
  json_typeof(false::text::json) AS "json type of true" ;

In the above query:

We have provided an Integer and floating point number to the json_typeof() function and typecased them into TEXT and then JSON. The query is valid and returns the following result.

This is how the json_typeof() function works with different JSON values.

Understanding PostgreSQL jsonb_typeof() Function

The jsonb_typeof() function works the same as the json_typeof() function does. It takes a JSONB value and returns its type as a string. The basic syntax of jsonb_typeof() function is given as:

jsonb_typeof(jsonb_val JSONB)

In the above syntax:

● The jsonb_typeof() function takes a JSONB value as input.

● jsonb_typeof() function gives the data type of the provided JSONB value as a string/text.

● If the function is provided NULL, the NULL value will be returned.

We can understand the workings of the jsonb_typeof() function using some examples.

Example 1: Understanding the jsonb_typeof() Function in PostgreSQL

To learn, how the jsonb_typeof() function works, we can execute the following query:

SELECT
  jsonb_typeof('"Command Prompt"') AS " jsonbType of Command Prompt",
  jsonb_typeof('true') AS "jsonbType of true",
  jsonb_typeof('[0,7,5,3]') AS "jsonbType of [0,7,5,3]",
  jsonb_typeof('973.8383') AS "jsonbType of 973.8383",
  jsonb_typeof('{"value":6}') AS "jsonbType of Command Prompt {""value"":6}",
  jsonb_typeof('false') AS "jsonbType of false",
  jsonb_typeof('null') AS "jsonbType of null";

In the above query, we have specified different types of JSONB values in the jsonb_typeof() function. The function will return the data types of all the JSONB values like this:

It is clear from the above output that the jsonb_typeof() function has returned the data type of the provided JSONB values in the TEXT data type.

Example 2: Understanding the jsonb_typeof() function With NULL

When NULL is provided in the quotes, the NULL is returned as a string. This can also be proved using the IS NULL statement. The “IS NULL” statement returns true if the expression gives NULL. The following can be the query written in this regard.

SELECT jsonb_typeof('null') IS NULL AS "jsonb type of null";

The output of this query is:

The IS NULL statement returned false because giving the ‘null’ to the jsonb_typeof() function will give the string NULL.

However, if we simply provide the NULL to the jsonb_typeof() function, we will get the NULL value, not the string NULL, like this.

SELECT jsonb_typeof(NULL) AS "jsonb type of NULL";

The output of this will be:

Now if we apply the IS NULL statement on it, this will return true because the value for this case is NULL.

SELECT jsonb_typeof(NULL) IS NULL AS "jsonb type of null";

Executing this query will give:

The query returned “true”, which means that the jsonb_typeof() function when applied on NULL gives null.

Example 3: Understanding the jsonb_typeof() Function in PostgreSQL

The numeric JSONB values that are to be provided to the jsonb_typeof() function should be enclosed in the single quotes to get its data type using the jsonb_typeof() function. The second thing we can do to the numeric type JSONB values is cast the provided value to the TEXT first and then to the JSONB data type. The same can be done to boolean values as well. The below query illustrates the concept.

SELECT 
  jsonb_typeof(46858::text::jsonb) AS "jsonb type of 46858",
  jsonb_typeof(8494.453::text::jsonb) AS "jsonb type of 973.8383",
  jsonb_typeof(true::text::jsonb) AS "jsonb type of true" ;

In the above query:

We have provided an Integer and floating point number to the jsonb_typeof() function and typecast them into TEXT and then JSONB. The query is valid and returns the following result.

This is how the jsonb_typeof() function works with different JSONB values.

Conclusion

The PostgreSQL json_typeof() function and the jsonb_typeof() function are used to get the data types of the JSON and JSONB values, respectively, in the string format. The JSON and JSONB values are provided to the json_typeof() function and the jsonb_typeof() function respectively. The sections above demonstrated the working of the json_typeof() function and the jsonb_typeof() function with the help of examples.