postgres data types jsonb json

Use jsonb format. Json is only for backward compatibility. Many functions and operators only exist for the binary representation. An example of such a function is jsonb_pretty, which improves the readability of the data.

Displaying and creating JSON documents

row_to_json function turn every rów into one JSON document . Often, we want the

VALUES (1, 2, 3), (4, 5, 6);
 column1 | column2 | column3
---------+---------+---------
       1 |       2 |       3
       4 |       5 |       6
(2 rows) 
SELECT row_to_json(x)
    FROM (VALUES (1, 2, 3), (4, 5, 6)) AS x;
              row_to_json
---------------------------------------
 {"column1":1,"column2":2,"column3":3}
 {"column1":4,"column2":5,"column3":6}

json_agg function turn entire set into one JSON document

SELECT json_agg(x) FROM (VALUES (1, 2, 3), (4, 5, 6)) AS x; 
                 json_agg
------------------------------------------
 [{"column1":1,"column2":2,"column3":3}, +
  {"column1":4,"column2":5,"column3":6}]
(1 row)

jsonb_pretty function helps us to properly format the output

SELECT jsonb_pretty(json_agg(x)::jsonb)
    FROM   (VALUES (1, 2, 3), (4, 5, 6)) AS x;
    jsonb_pretty
-----------------------
 [                    +
     {                +
         "column1": 1,+
         "column2": 2,+
         "column3": 3 +
     },               +
     {                +
         "column1": 4,+
         "column2": 5,+
         "column3": 6 +
     }                +
 ]
(1 row)

Keep in mind, the + symbols are again injected by psql and are not present in the result set sent by the database.

Turn json document into sql row

json_populate_record function helps us to map suitable JSON to the table:

CREATE TABLE t_json (x int, y int);
SELECT *
    FROM json_populate_record(NULL::t_json, '{"x":54,"y":65}');
 x  | y
----+----
 54 | 65
(1 row)

The trick is to pass NULL as a parameter and cast it to the name of the table we want to map the document to, which is really powerful. If you have a table that matches your JSON document, at least partially, you are mostly done. It is really easy to insert data under those circumstances:

test=# INSERT INTO t_json
    SELECT *
    FROM  json_populate_record(NULL::t_json, 
         '{"x":54,"y":65}');
INSERT 0 1
Here is proof that the data has been added:

test=# SELECT * FROM t_json;
 x  | y
----+----
 54 | 65
(1 row)

Accessing a JSON document

create table js(id serial primary key, extra jsonb);
insert into js(extra) 
 values ('[1, 2, 3, 4]'), 
        ('[2, 3, 5, 8]'), 
          ('{"key": "value"}');
select * from js where extra @> '2';
select * from js where extra @> '[2,4]';

create index on js using gin (extra jsonb_path_ops);

select jsonb_pretty(data)
 from magic.cards
 where data @> '{
 "type":"Enchantment",
 "artist":"Jim Murray",
 "colors":["White"]
 }';

@> operator reads contains and implements JSON searches, with support from a specialized GIN index if one has been created.
jsonb_pretty() function does what we can expect from its name, and the query returns magic.cards rows that match the JSON criteria for given type, artist and colors key, all as a pretty printed JSON document.
-> operator will help us to find a subtree and return this part.
->> operator help return Real value without enclosed as json document
jsonb_each function will loop over the subtree and return all elements as a composite type (the record data type).
jsonb_each_text function will do loop over the subtree and extract text.
jsonb_object_keys function to just extract the keys in the document or subtree.

create table post_json (jsondata jsonb);

insert into post_json(jsondata)
select row_to_json(q) as json_data from (
select p.pk,p.title,string_agg(t.tag,',') as tag
from posts p
left join j_posts_tags jpt on p.pk=jpt.post_pk
left join tags t on jpt.tag_pk=t.pk
group by 1,2 order by 1)

select jsonb_pretty(jsondata) from post_json;

select jsonb_pretty(jsondata) from post_json where jsondata @>'{"tag":"Database"}';

PostgreSQL provides two native operators -> and ->> to help you query JSON data. The operator -> returns JSON object field as JSON. The operator ->> returns JSON object field as text. -> returns json (or jsonb) and ->> returns text

with t (jo, ja) as (values
    ('{"a":"b"}'::jsonb,('[1,2]')::jsonb)
)
select
    pg_typeof(jo -> 'a'), pg_typeof(jo ->> 'a'),
    pg_typeof(ja -> 1), pg_typeof(ja ->> 1)
from t
;
 pg_typeof | pg_typeof | pg_typeof | pg_typeof 
-----------+-----------+-----------+-----------
 jsonb     | text      | jsonb     | text

we now have 2 different kinds of null:

(null) postgres null type
null json/b null type

create table json_test (
  id integer,
  val JSONB
);

INSERT INTO json_test (id, val) values
(1, jsonb_build_object('member', null)),
(2, jsonb_build_object('member', 12)),
(3, null);

SELECT id,
  val -> 'member'  as arrow,
  pg_typeof(val -> 'member')  as arrow_pg_type,
  val -> 'member' IS NULL as arrow_is_null,
  val ->> 'member' as dbl_arrow,
  pg_typeof(val ->> 'member')  as dbl_arrow_pg_type,
  val ->> 'member' IS NULL as dbl_arrow_is_null,
  CASE WHEN jsonb_typeof(val -> 'member') = 'null' THEN true ELSE false END as is_json_null
from json_test;

Notes:

for {"member": null}:
- val -> 'member' IS NULL is false
- val ->> 'member' IS NULL is true
is_json_null can be used to get only the json-null condition

Postgres has two JSON datatypes: JSONB and JSON. JSONB is an optimized binary version of JSON, which is slower to store, but is optimized for querying and processing. JSON is an exact copy of the data with limited query functionality.

CREATE TABLE calendar (
    id uuid DEFAULT uuid_generate_v4() NOT NULL,
    date date,
    user_id uuid NOT NULL,
    weight numeric,
    notes text,
    food_log jsonb,
    water_log jsonb,
    exercise_log jsonb
);
-- (Optional) - create a foreign key relationship for the user_id field 
ALTER TABLE ONLY calendar
    ADD CONSTRAINT calendar_user_id_fkey FOREIGN KEY (user_id) REFERENCES auth.users(id);

insert into calendar (date, user_id, weight, notes, food_log, water_log, exercise_log)
values (
   '2022-01-01', 
   'xyz', 
   172.6, 
   'This new diet is awesome!',
   '[
      { "title": "Apple", "calories": 72, "meal": "Breakfast"},
      { "title": "Oatmeal", "calories": 146, "meal": "Breakfast"},
      { "title": "Sandwich", "calories": 445, "meal": "Lunch"},
      { "title": "Chips", "calories": 280, "meal": "Lunch"},
      { "title": "Cookie", "calories": 108, "meal": "Lunch"},
      { "title": "Mixed Nuts", "calories": 175, "meal": "Snack"},
      { "title": "Pasta/Sauce", "calories": 380, "meal": "Dinner"},
      { "title": "Garlic Bread", "calories": 200, "meal": "Dinner"},
      { "title": "Broccoli", "calories": 32, "meal": "Dinner"}
     ]',
   '[
      {"time": "08:15", "qty": 1},
      {"time": "09:31", "qty": 1},
      {"time": "10:42", "qty": 2},
      {"time": "10:42", "qty": 2},
      {"time": "12:07", "qty": 1},
      {"time": "14:58", "qty": 1},
      {"time": "17:15", "qty": 1},
      {"time": "18:40", "qty": 1},
      {"time": "19:05", "qty": 1}
    ]',
   '[
      {"time": "11:02", "duration": 0.5, "type": "Walking"}
    ]'
);

jsonb_to_recordset

Using jsonb_to_recordset, we can turn a array into a set of records:

SELECT 
	* 
FROM jsonb_to_recordset('[{"name": "batman"}, {"name": "superman"}]'::jsonb) AS x(name TEXT);

   name   
----------
 batman
 superman
(2 rows)

convert from set of records back to JSON

SELECT
	to_jsonb(employees) 
FROM employees
LIMIT 5;

 SELECT 
	to_jsonb(truncated_employees) 
FROM (
	SELECT first_name, last_name FROM employees LIMIT 10
) AS truncated_employees;

Extracting data from JSONB data

SELECT 
	('{"name": "batman", "superpower": "fighting skills"}'::jsonb)->'name';

We used array notation [0] to return the first element of the array, then used the ->> operator to return the value of the name attribute.

For top-level array, use the 0 as the value to be retrieved:

SELECT 
	('["batman", "superman"]'::jsonb)->>0;

Postgres JSON functions, - a large list of JSON manipulation and querying operators.

Above are examples using operators, and below, we will use JSONPath. JSONPath allows for more expressive manipulation and extracting.

SELECT  	jsonb_path_query(('[{"name": "batman"}, {"name": "superman"}]'::jsonb), '$[0].name');

JSON subscripting

can use subscripts to iterate through JSON key pairs and fetch corresponding values. For example, using this capability, nested JSON fields can be quickly traversed to retrieve values to reconstruct application objects.

select (
  '{ "PostgreSQL": { "release": 14 }}'::jsonb
)['PostgreSQL']['release'];

 jsonb
-------
 14

JSON_TABLE

allows jsonb data to be treated as a table and thus used in a FROM clause like other tabular data. Data can be selected from the jsonb using jsonpath expressions, and hoisted out of nested structures in the jsonb to form multiple rows, more or less like an outer join.

SQL/JSON query functions

https://www.depesz.com/2022/04/01/waiting-for-postgresql-15-sql-json-query-functions/
JSON_EXISTS()
JSON_QUERY()
JSON_VALUE()

Test

Provide feedback

Saved searches

Use saved searches to filter your results more quickly