JSON Primitive Types and Corresponding PostgreSQL Types
Tags: postgres, json, jsonb, sql
Published: 2022-08-02 19:36:00

How does PostgreSQL store data inside jsonb and map them to its data type?
first Postgres has a special data type for JSON it is different from PostgreSQL data types _-like text,  numeric and so on …- _and we have different methods to check those types for PostgreSQL type we use `pg_typeof`, and for jsonb type, we use `jsonb_typeof`

#####Remember that in JSON format

- keys should be **strings**
- values can be strings, numbers, boolean, null, array, or another JSON object

---

this table from [Postgrse documentation](https://www.postgresql.org/docs/current/datatype-json.html) 
 summarize the main JSON data type in Postgres

| JSON type        | PostgreSQL type           | Notes  |
|------------- |:-------------:| -----|
| string      | text | \u0000 is disallowed, as are Unicode escapes representing characters not available in the database encoding |
| number      | numeric      |   NaN and infinity values are disallowed |
| boolean | boolean      |    Only lowercase true and false spellings are accepted |
| null | (none)      |    SQL NULL is a different concept |

let’s dive and explain each JSON type in PostgreSQL

---

##jsonb string:
 you can store any text value except `\u0000` _- which a Unicode escapes representing characters that represent `NULL`_

to show the meaning of  Unicode characters in Postgres use `E` in front of text like

```sql
----------------------- jsonb string ---------------------------
select E'\u0001' as value;
-- value 
-- -------
-- \x01
-- (1 row)

select E'\u0000' as value;
-- ERROR: invalid Unicode escape value at or near "E'\u0000"
-- LINE 1: select E'\u0000' as value;

SELECT '1234\u0000';
-- ?column? 
-- ------------
-- 1234\u0000
-- (1 row)

SELECT E'My star \u2B50';
--  ?column?  
-- -----------
--  My star ⭐
-- (1 row)

SELECT E'1234\u0000';
-- ERROR: invalid Unicode escape value at or near "E'1234\u0000"
-- LINE 1: SELECT E'1234\u0000';

SELECT '"1234\u0000"'::jsonb;
-- ERROR: unsupported Unicode escape sequence
-- LINE 1: SELECT '"1234\u0000"'::jsonb;
-- ^
-- DETAIL: \u0000 cannot be converted to text.
-- CONTEXT: JSON data, line 1: ...

SELECT '"My face \u2B50"'::jsonb;
--     jsonb    
-- -------------
--  "My face ⭐"
-- (1 row)
```
***
##jsonb number : 
you can store any positive or negative numbers just except **NaN** and **infinity**
```sql
----------------------- jsonb number ---------------------------
SELECT jsonb_typeof('1'::jsonb);
-- jsonb_typeof 
-- --------------
-- number
-- (1 row)

SELECT jsonb_typeof('-1'::jsonb);
-- jsonb_typeof 
-- --------------
-- number
-- (1 row)
```

**infinity**: can represent like  `infinity`,`- infinity`,`inf`, `-inf`
```sql
SELECT jsonb_typeof('inf'::jsonb);
-- ERROR: invalid input syntax for type json
-- LINE 1: SELECT jsonb_typeof('inf'::jsonb);
-- ^
-- DETAIL: Token "inf" is invalid.
-- CONTEXT: JSON data, line 1: inf

SELECT jsonb_typeof('-inf'::jsonb);
-- ERROR: invalid input syntax for type json
-- LINE 1: SELECT jsonb_typeof('-inf'::jsonb);
-- ^
-- DETAIL: Token "-inf" is invalid.
-- CONTEXT: JSON data, line 1: -inf
```

**NAN**: (not a number) value is used to represent undefined calculational results like
```sql
select 'infinity'::float / 'infinity'::float;
-- ?column? 
-- ----------
-- NaN
-- (1 row)

SELECT jsonb_typeof('NAN'::jsonb);
-- ERROR: invalid input syntax for type json
-- LINE 1: SELECT jsonb_typeof('NAN'::jsonb);
-- ^
-- DETAIL: Token "NAN" is invalid.
-- CONTEXT: JSON data, line 1: NAN
```

***

##jsonb boolean: 
Only lowercase `true` and `false` are considered as boolean
```sql
SELECT 'true'::jsonb;
-- jsonb 
-- -------
-- true
-- (1 row)

SELECT jsonb_typeof('true'::jsonb);
-- jsonb_typeof 
-- --------------
-- boolean
-- (1 row)

SELECT jsonb_typeof('false'::jsonb);
-- jsonb_typeof 
-- --------------
-- boolean
-- (1 row)

SELECT jsonb_typeof('False'::jsonb);
-- ERROR: invalid input syntax for type json
-- LINE 1: SELECT jsonb_typeof('False'::jsonb);
-- ^
-- DETAIL: Token "False" is invalid.
-- CONTEXT: JSON data, line 1: False

SELECT jsonb_typeof('True'::jsonb);
-- ERROR: invalid input syntax for type json
-- LINE 1: SELECT jsonb_typeof('True'::jsonb);
-- ^
-- DETAIL: Token "True" is invalid.
-- CONTEXT: JSON data, line 1: True

SELECT jsonb_typeof('"True"'::jsonb);
-- jsonb_typeof 
-- --------------
-- string
-- (1 row)
```

***

##null and Null in jsonb and Postgres: 
>jsonb null is different from NULL in Postgres as SQL’s null means *“of unknown value”* and JSON’s null means *“empty/no value”.* 

let’s take some examples to explain this:

_Note : use `->` to return the value of key in jsonb as jsonb type and `->>` convert the value of key as text and return text.    . By the way I explained this in next post in this series_

```sql
select '{"a": 1, "b": null}'::jsonb->'c';
-- ?column? 
-- ----------
-- (1 row)
```
`c` is not keys in jsonb so if we ask for it is value Postgres will search for it in `'{"a": 1, "b": null}'` and as it can not find it so Postgres does not know the value of `c` so it says it is  unknown value means it is `NULL`, as `c` does not there

_Note : in select Null does not shown_
```sql
select '{"a": 1, "b": null}'::jsonb->'c' IS NULL;
-- ?column? 
-- ----------
-- t
-- (1 row)
```
but what about `b` ?! what’s the value of `b`? 
```sql
select '{"a": 1, "b": null}'::jsonb->'b';
-- ?column? 
-- ----------
-- null
-- (1 row)
```

here `b` is key inside jsonb and it has value but it is `null` ( empty/no value ) so if Postgres look for b it can find it and return its value as `b` **not** `NULL` because it has value and it does not with unknown value as  Postgres really can find it and knows its value
```sql
select '{"a": 1, "b": null}'::jsonb->'b' IS NULL;
-- ?column? 
-- ----------
-- f
-- (1 row)
```

notice that in previous examples we use `->` which is used to return the value of key in jsonb as it is but what if we use `->>` that will convert it to text … things become tricky here look to these examples
```sql
select '{"a": 1, "b": null}'::jsonb->>'b' IS Null;
-- ?column? 
-- ----------
-- t
-- (1 row)

select '{"a": 1, "b": null}'::jsonb->>'c' IS Null;
-- ?column? 
-- ----------
-- t
-- (1 row)
```
now the value of `c` and `b` **IS** `NULL` **why**?
Here explain from PostgreSQL

>One of the design principles of PostgreSQL, however, is that casting anything to text should give something parsable back to the original value (whenever possible).

So if we will convert `null` to text it will be `NULL` as it is the nearest value to it that we can use to re-parse it back … 

**but why we can not just convert `null` to be `‘null’` as string with 2 single quotes ??!!**

doing something like that will make confused between `‘null’`string value that stored as strung in keys like
```sql
select '{"a": 1, "b": null}'::jsonb->>'b';
-- ?column? 
-- ----------
-- (1 row)

select '{"a": 1, "b": "null"}'::jsonb->>'b';
-- ?column? 
-- ----------
-- null
-- (1 row)

select pg_typeof('{"a": 1, "b": "null"}'::jsonb->>'b');
-- pg_typeof 
-- -----------
-- text
-- (1 row)

select pg_typeof('{"a": 1, "b": null}'::jsonb->>'b');
-- pg_typeof 
-- -----------
-- text
-- (1 row)
```
so ‘null’ to text = ‘null’ and if you re-parse ‘null’ to original type it will string ‘null’

but null to text = NULL and if you re-parse null to original type it will null 

this lead us to golden point here so **if you want to check that if key does not there or its value is null use `->>` to get value of that key and check if `IS NULL` ….**

same if you want to say **if value is there for keys this means the key is there or its value is not null use `->>` to get value of that key and check if `IS NOT NULL`**


**Reference** : Check this for more details
[PostgreSQL_and_null_value](http://mbork.pl/2020-02-15_PostgreSQL_and_null_value)

---

all code examples in jsonb series in my GitHub account 💻[jsonb_posts](https://github.com/SaraAlhaddadi/jsonb_posts)