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
Top comments (0)