postgres data types jsonb json - ghdrako/doc_snipets GitHub Wiki

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.

JSON index

Index types use with JSON

  1. GIN (Generalized Inverted Index): This index is used to index the keys and values of JSON data. It can be used for simple lookups, as well as for more complex queries that involve nested JSON structures.
  2. GIST (Generalized Search Tree): This index is used to index the entire JSON data object. It can be used for more complex queries that involve searching for specific values within the JSON data.
CREATE INDEX profile_data_idx ON profiles_json USING gin(emp_data);
EXPLAIN ANALYZE SELECT * FROM profiles_json WHERE emp_data ? 'visa_usa';

You can also create the index on JSON members instead of the keys:

postgres=# CREATE INDEX profile_data_name_idx2 ON profiles_json ( (emp_ data->>'name') );
postgres=# EXPLAIN ANALYZE SELECT * FROM profiles_json WHERE emp_data ->> 'name' = 'foo';

JSON use cases

  • Storing Flexible Schema Data - For example, a survey application that collects answers from users could store the answers in JSON format,
  • Storing Metadata - For example, an image gallery application could store metadata for each image in JSON format
CREATE TABLE images (  id SERIAL PRIMARY KEY,  metadata JSONB );
INSERT INTO images (metadata) VALUES ('{"resolution": "1920x1080", "size": "2MB", "created_at": "2022-01-01"}');
postgres=# SELECT metadata->>'resolution' as resolution, metadata->>'size' as size FROM images;
 resolution | size
 ------------+------
 1920x1080 | 2MB
 (1 row)
  • Storing Configuration Data
CREATE TABLE configurations (  id SERIAL PRIMARY KEY,  data JSONB );
I
INSERT INTO configuration VALUES ('{"development": {"database": {"host": "localhost", "port": 5432}}, "production": {"database": {"host": "example.com", "port": 5432}}}');

postgres=# SELECT data->'development'->'database'->'host' as dev_ host, 
                  data->'production'->'database'->'host' as prod_host 
             FROM configurations;
 dev_host | prod_host
 -------------+---------------
 "localhost" | "example.com"
 (1 row) This
  • Storing User Preferences - For example, a social media application could store each user’s preferences for news feed content and notification settings in JSON format.
CREATE TABLE user_preferences (  id SERIAL PRIMARY KEY,  preferences JSONB );
INSERT INTO user_preferences (preferences) VALUES ('{"news_feed": {"show_images": true, "show_videos": true}, "notifications": {"email": true, "push": false}}');
postgres=# SELECT preferences->'news_feed'->'show_images' as show_images, preferences->'notifications'->'email' as email FROM user_preferences;
show_images | email
 -------------+-------
 true | true 
(1 row)
  • Storing NoSQL-like Data - For example, a document-oriented database could use JSON to store data in a document format while still taking advantage of PostgreSQL’s powerful indexing and querying capabilities.
CREATE TABLE documents (  id SERIAL PRIMARY KEY,  content JSONB );
INSERT INTO documents (content) VALUES ('{"title": "My document", "content": "This is my document text", "metadata": {"tags": ["postgresql", "json"], "views": 100}}');

postgres=# SELECT content->>'title' as title, content->> 'content' as content, content#>'{metadata,tags}' as tags, content#>>'{metadata,views}' as views FROM documents;
 title | content | tags |  views
 -------------+--------------------------+------------------------+ ------- 
 My document | This is my document text | ["postgresql", "json"] |  100
 (1 row)

JSON operator

CREATE TABLE test_table (  id SERIAL PRIMARY KEY,  data JSON );
INSERT INTO test_table (data) VALUES ('{"name": "my_name", "age": 30}');
SELECT data->'name' as name, data->'age' as age FROM test_table; -- operator ->
SELECT pg_typeof(data->'name') as name, pg_typeof(data->'age') as age FROM test_table;
 name | age 
------+------ 
 json | json 
(1 row)

The -> operator gets the object field by key. If you want to use the result to type cast to any other data type like INT for further purposes, it does not allow with the -> operator as it returns a key:

postgres=# SELECT (data->'name')::varchar as name, (data->'age')::int as age FROM test_table;
ERROR: cannot cast type json to integer LINE 1: ...ECT (data->'name')::varchar as name, (data->'age')::int as a...

You can use the ->> operator when you have a use case like this. This operator gets the field in text format so that you can type cast to an allowed type as required:

postgres=# SELECT (data->>'name')::varchar as name, (data->>'age')::int as age FROM test_table;
 name | age 
---------+----- 
my_name | 30 
(1 row)

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}

In addition to the -> and ->> operators, PostgreSQL provides several other operators for working with JSON data, including #>, #>>, and @>. These operators allow users to extract specific fields, navigate nested JSON structures, and perform comparisons.

#> and #>> operators are used to get the fields from any array of values in a JSON string.

postgres=# INSERT INTO test_table(data) VALUES ('{"name":["foo","bar"], "age":[40,50]}');
postgres=# SELECT data#>'{name,0}' as name, data#>'{age,0}' as age FROM test_table WHERE id=2;
 name | age
 -------+-----
 "foo" | 40
 (1 row) 
postgres=# SELECT pg_typeof(data#>'{name,0}') as name, pg_ typeof(data#>'{age,0}') as age FROM test_table WHERE id=2;
 name | age
 ------+------
 json | json
 (1 row) 
postgres=# SELECT data#>>'{name,0}' as name, data#>>'{age,0}' as age FROM test_table WHERE id=2;
name | age
 ------+-----
 foo | 40
 (1 row) 

postgres=# SELECT pg_typeof(data#>>'{name,0}') as name, pg_ typeof(data#>>'{age,0}') as age FROM test_table WHERE id=2;
 name | age
 ------+------
 text | text 
(1 row)
  • 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"}
    ]'
);

JSON query path

You can use json_path_query to retrieve all JSON items returned by the JSON path corresponding to the provided JSON value:

postgres=# SELECT jsonb_path_query(emp_data, '$.languages.special') FROM profiles_json;
 jsonb_path_query
 --------------------------------------------
 ["brailey", "some_other_special_langugae"] 
(1 row)

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;

Building JSON

Use json_object to build the json string using a text array:

postgres=# SELECT json_object('{"id", "1", "name", "foo", "exp", "4"}');
 json_object
 -------------------------------------------
 {"id" : "1", "name" : "foo", "exp" : "4"} 
(1 row)

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