JSON Primitive Types and Corresponding PostgreSQL Types

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

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

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

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

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

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

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

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

but what about b ?! what’s the value of b?

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

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

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

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

---

all code examples in jsonb series in my GitHub account 💻jsonb_posts