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

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 usepg_typeof, and for jsonb type, we usejsonb_typeof

Remember that in JSON format

• keys should bestrings
• values can be strings, numbers, boolean, null, array, or another JSON object

this table from Postgres documentation 

summarize the main JSON data type in Postgres

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 representNULL

to show the meaning of Unicode characters in Postgres useEin 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 exceptNaNandinfinity

----------------------- 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 likeinfinity,- 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 lowercasetrueandfalseare 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)

cis 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 ofcso it says it is unknown value means it isNULL, ascdoes not there

Note : in select Null does not shown

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

but what aboutb?! what’s the value ofb?

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

herebis key inside jsonb and it has value but it isnull( empty/no value ) so if Postgres look for b it can find it and return its value asbnotNULLbecause 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 ofcandbISNULLwhy?

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 convertnullto text it will beNULLas it is the nearest value to it that we can use to re-parse it back …

but why we can not just convertnullto 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 soif 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 sayif 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