# Understanding PostgreSQL jsonb_typeof() and json_typeof()

> 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 …

There are several JSON functions that PostgreSQL offers that are applicable to the JSON and JSONB data types. The [JSONB](<https://www.commandprompt.com/education/jsonb-data-type-in-postgresql/>) data type is the binary form/version of the [JSON](<https://www.commandprompt.com/education/working-with-postgresql-json-data/>) 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](<https://www.commandprompt.com/education/postgresql-is-null-operatorcondition/>) 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](<https://www.commandprompt.com/education/postgresql-is-null-operatorcondition/>) 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.

---
[View this page online](https://www.commandprompt.com/education/understanding-postgresql-jsonb_typeof-and-json_typeof/)