Json type

instructions

According to RFC 7159, the JSON data type is used to store JSON (JavaScript Object Notation) data. This data can also be stored as TEXT, but the advantage of the JSON data type is that you can force every stored value to conform to JSON rules. There are also a number of JSON-related functions and operators available for data stored in these data types

PostgreSQL supports two JSON data types: JSON and JSONB. They accept almost the exact same set of values as input. The biggest difference is efficiency. The JSON data type stores an exact copy of the input text, which the handler must reparse each time it executes. Jsonb data, on the other hand, is stored in a decomposed binary format that is slightly slower to input because of the additional transformations required. But JSONB is much faster in processing because it does not require reparsing.

Important: JSONB supports indexing

Since the JSON type stores an exact copy of the input text, it is stored in the order of Spaces and keys within the JSON object. If the JSON object in a value contains the same key more than once, all key/value pairs are retained (** the handler treats the last value as valid **).

Jsonb does not retain Spaces, does not retain order of object keys, and does not retain duplicate object keys. If duplicate keys are specified in the input, only the last value is retained.

It is recommended to store JSON data as JSONB

When converting textual JSON input to JSONB, the JSON base type (RFC 7159) is mapped to the native PostgreSQL type. Therefore, jSONB data has some minor additional constraints. For example, jSONb will reject numbers outside the PostgreSQL Numeric data type range, whereas JSON will not.

JSON basic type and corresponding PostgreSQL type

JSON basic types PostgreSQL type annotation
string text Don’t allow\u0000If the database encoding is not UTF8, the same is true for non-ASCII Unicode escapes
number numeric Don’t allowNaN 和 infinityvalue
boolean boolean Lower case onlytrueandfalsespelling
null (not) SQL NULLIt’s a different concept

Json input and output syntax

-- Simple scalars/basic values
The base value can be a number, a quoted string, true, false, or null
SELECT '5': :json;

Array with zero or more elements (elements need not be the same type)
SELECT '[1, 2, "foo", null]': :json;

-- An object containing key-value pairs
Note that the object key must always be a quoted string
SELECT '{" bar ":" baz ", "balance" : 7.77, "active" : false}': :json;

Arrays and objects can be nested arbitrarily
SELECT '{"foo": [true, "bar"], "tags": {"a": 1, "b": null}}': :json;

-- "-- >" --" -- >" -- "-- >
select '{"nickname": "goodspeed", "avatar": "avatar_url", "tags": ["python", "golang", "db"]}': :json->'nickname' as nickname;
 nickname
-------------
 "goodspeed"

-- "-- >>" --" -- >>" -- "-- >>
select '{"nickname": "goodspeed", "avatar": "avatar_url", "tags": ["python", "golang", "db"]}': :json->>'nickname' as nickname;
 nickname
-----------
 goodspeed
 
-- "-- >" --" -- >" -- "-- >
select '{"nickname": "goodspeed", "avatar": "avatar_url", "tags": ["python", "golang", "db"]}'::jsonb->'nickname' as nickname;
 nickname
-------------
 "goodspeed"

-- "-- >>" --" -- >>" -- "-- >>
select '{"nickname": "goodspeed", "avatar": "avatar_url", "tags": ["python", "golang", "db"]}'::jsonb->>'nickname' as nickname;
 nickname
-----------
 goodspeed
Copy the code

When a JSON value is entered and then output without any additional processing, JSON outputs exactly the same text as the input, while JSONB does not retain semantically meaningless details

SELECT "{" bar" : "baz", "balance" : 7.77, "active" : false} ': : json. json ------------------------------------------------- {"bar": "baz", "balance": 7.77, "active":false} -- jSONb does not retain semantic details and the key order is not consistent with the original data SELECT '{"bar": "baz", "balance": 7.77, the "active" : false} ': : jsonb; Jsonb -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- {" bar ":" baz ", "active" : false, "balance" : 7.77}Copy the code

Json Query Syntax

When using JSON documents, it is recommended to store the JSON document as a fixed structure. (This structure is optional, but having a predictable structure makes querying collections easier.) Recommendations for designing JSON documents: any update requires a row-level lock on the entire row. To reduce lock contention, JSON documents should each represent one atom of data (non-separable, independently modifiable data on business rules).

These commonly used comparison operators are only valid for JSONB, not JSON

Common comparison operators

The operator describe
< Less than
> Is greater than
< = Less than or equal to
> = Greater than or equal to
= Is equal to the
<> or ! = Is not equal to

Inclusion and existence

Json data query (for JSONB)

Json and jSONb operators

- > and - > >The operator

Use ->> to find out the data is a JSON object

* * * * * * * * * * * * * * * * * * * * * *
select '{"nickname": "gs", "avatar": "avatar_url", "tags": ["python", "golang", "db"]}': :json->>'nickname' = 'gs';
select '{"nickname": "gs", "avatar": "avatar_url", "tags": ["python", "golang", "db"]}'::jsonb->>'nickname' = 'gs';

-- If you use a -> query, it will throw an exception whether the match is 'gs' of type text or 'gs' of type JSON ::json. Json does not support the equal (=) operator
select '{"nickname": "gs", "avatar": "avatar_url", "tags": ["python", "golang", "db"]}': :json->'nickname' = '"gs"';
ERROR:  operator does not exist: json = unknown
The -- JSON type does not support the "=" operator
select '{"nickname": "gs", "avatar": "avatar_url", "tags": ["python", "golang", "db"]}': :json->'nickname' = '"gs"': :json;
ERROR:  operator does not exist: json = json

-- jsonb = jSONb -- Jsonb = JSONb
select '{"nickname": "gs", "avatar": "avatar_url", "tags": ["python", "golang", "db"]}'::jsonb->'nickname' = '"gs"';
Copy the code
# # > and > >The operator

Use #>> to find out the data is text. Use #> to find out the data is JSON object

select '{"nickname": "gs", "avatar": "avatar_url", "tags": ["python", "golang", "db"]}': :json# >'{tags,0}' as tag;
   tag
----------
 "python"

select '{"nickname": "gs", "avatar": "avatar_url", "tags": ["python", "golang", "db"]}': :json# > >'{tags,0}' as tag;
  tag
--------
 python
 
select '{"nickname": "gs", "avatar": "avatar_url", "tags": ["python", "golang", "db"]}'::jsonb#>'{tags,0}' = '"python"'; ? column?----------
 t
select '{"nickname": "gs", "avatar": "avatar_url", "tags": ["python", "golang", "db"]}'::jsonb#>>'{tags,0}' = 'python'; ? column?----------
 t

select '{"nickname": "gs", "avatar": "avatar_url", "tags": ["python", "golang", "db"]}': :json# > >'{tags,0}' = 'python'; ? column?----------
 t
-- will throw an error, whether the match is' Python 'of type text or '" Python "' of type JSON ::json. Json does not support the equal (=) operator
select '{"nickname": "gs", "avatar": "avatar_url", "tags": ["python", "golang", "db"]}': :json# >'{tags,0}' = '"python"';
ERROR:  operator does not exist: json = unknown
Copy the code
Jsonb data query (not available for JSON)

** Additional jsonb operators **

@ >The operator
-- Nickname indicates the nickname user
select '{"nickname": "gs", "avatar": "avatar_url", "tags": ["python", "golang", "db"]}'::jsonb @> '{"nickname": "gs"}'::jsonb;

-- is equivalent to the following query
The match should also be a JSON object
select '{"nickname": "gs", "avatar": "avatar_url", "tags": ["python", "golang", "db"]}'::jsonb->'nickname' = '"gs"';
select '{"nickname": "gs", "avatar": "avatar_url", "tags": ["python", "golang", "db"]}'::jsonb->>'nickname' = 'gs';

Query data with Python and Golang tags
select '{"nickname": "gs", "avatar": "avatar_url", "tags": ["python", "golang", "db"]}'::jsonb @> '{"tags": ["python", "golang"]}'; ? column?----------
 t
Copy the code
?The operator,? |Operators, and? &The operator
Query the user with the avatar attribute
select '{"nickname": "gs", "avatar": "avatar_url", "tags": ["python", "golang", "db"]}'::jsonb ? 'avatar';
Query the data that has the Avatar attribute and the Avatar data is not empty
select '{"nickname": "gs", "avatar": null, "tags": ["python", "golang", "db"]}'::jsonb->>'avatar' is not null;

Query data with Avatar or tags
select '{"nickname": "gs", "tags": ["python", "golang", "db"]}'::jsonb ? |array['avatar'.'tags']; ? column?----------
 t

Query users with both Avatars and tags
select '{"nickname": "gs", "tags": ["python", "golang", "db"]}'::jsonb ? &array['avatar'.'tags']; ? column?----------
 f
 
 Query tags for data containing Python tags
 select '{"nickname": "gs", "avatar": "avatar_url", "tags": ["python", "golang", "db"]}'::jsonb->'tags' ? 'python'; ? column?----------
 t
Copy the code

Json update

-- Update the Account Content field (overridden)
update account set content = jsonb_set(content.'{}'.'{"nickname": "gs", "tags": ["python", "golang", "db"]}'.false);

Change nickanme to nickanme's user tag
update account set content = jsonb_set(content.'{tags}'.'["test", "psychology "]'.true) where content@ >'{"nickname": "nickname"}'::jsonb;

update account set content = jsonb_set(content.'{tags}'.'["test", "psychological "," medical "]'.true) where content@ >'{"nickname": "nickname"}'::jsonb;

-- update the value of weixin_mp in the Account Content field (if not created)

update account set content = jsonb_set(content.'{weixin_mp}'.'"weixin_mp5522bd28-ed4d-11e8-949c-7200014964f0"'.true) where id='5522bd28-ed4d-11e8-949c-7200014964f0';

-- Update account to remove weixin field from content (no exception will be thrown if there is no weixin field)
update account set content= content - 'weixin' where id='5522bd28-ed4d-11e8-949c-7200014964f0';
Copy the code

Json functions

jsonb_pretty

Return from_JSON as indent JSON text.

select jsonb_pretty('[{"f1":1,"f2":null},2,null,3]');
    jsonb_pretty
--------------------
 [                 +
     {             +
         "f1": 1,  +
         "f2": null+
     },            +
     2,            +
     null,         +
     3             +
 ]
(1 row)
Copy the code

jsonb_set

The jsonb_set() function takes the following parameters:

jsonb_set(target         jsonb,  // The data to be modified
          path           text[], // The data path
          new_value      jsonb,  / / the new data
          create_missing boolean default true)
Copy the code

If create_missing is true (the default is true) and the path specified by path does not exist in the target, then the target will contain either the path specified part, the new_value replacement part, or the new_value added part.

- select the target structure jsonb_pretty (' [{" f1 ": 1," f2 ": null}, 2] '); jsonb_pretty -------------------- [ + { + "f1": 1, + "f2": Null +}, + 2 +] -- update target 0th element key to f1, If there is no f1 ignore the select jsonb_set (' [{" f1 ": 1," f2 ": null}, 2, null, 3] ', '{0} f1,' [4] 2, false); jsonb_set --------------------------------------------- [{"f1": [2, 3, 4], "f2": Null}, 2, null, 3] -- update target 0th element key to f3, If f3 does not exist to create the select jsonb_set (' [{" f1 ": 1," f2 ": null}, 2) ', '{0, f3}', '[4] 2'); jsonb_set --------------------------------------------- [{"f1": 1, "f2": null, "f3": [2, 3, 4]}, 2] -- update target 0th element key to f3, If f3 is not overlook the select jsonb_set (' [{" f1 ": 1," f2 ": null}, 2) ', '{0, f3}', '[4] 2, false); jsonb_set --------------------------------------------- [{"f1": 1, "f2": null}, 2]Copy the code

For detailed JSON functions and operators, refer to the json functions and operators document

Jsonb performance analysis

Let’s use the following example to illustrate the query performance of JSON

Table structure

-- The ACCOUNT table ID uses the UUID type. You need to add the UUID osSP module first. CREATE EXTENSION IF NOT EXISTS "uuid-ossp"; -- create table create table account (id UUID NOT NULL PRIMARY KEY default uuid_generate_v1(), content jsonb, created_at timestamptz DEFAULT CURRENT_TIMESTAMP, updated_at timestamptz DEFAULT CURRENT_TIMESTAMP); json=> \d account Table "public.account" Column | Type | Collation | Nullable | Default --------------+--------------------------+-----------+----------+-------------------- id | uuid | | not null |uuid_generate_v1() content | jsonb | | | created_at | timestamp with time zone | | | CURRENT_TIMESTAMP updated_at | timestamp with time zone | | | CURRENT_TIMESTAMP Indexes: "account_pkey" PRIMARY KEY, btree (id)Copy the code

A good practice is to put creATED_AT and updatED_AT in the JSONB field as well, just for example

The data structure of content is:

content = {
    "nickname": {"type": "string"},
    "avatar": {"type": "string"},
    "weixin": {"type": "string"},
    "tags": {"type": "array", "items": {"type": "string"}},
}
Copy the code

To prepare data

Batch Insert data

Nickname avatar tags ["python", "golang", "c"] = 100W insert nickname avatar tags ["python", "golang", "c"
insert into account select uuid_generate_v1(), ('{"nickname": "nn-' || round(random()*20000000) | |'", "avatar": "avatar_url", "tags": ["python", "golang", "c"]}')::jsonb from (select * from generate_series(1.100000)) as tmp;

Nickname tags ["python", "golang"] insert 100W nickname tags ["python", "golang"
insert into account select uuid_generate_v1(), ('{"nickname": "nn-' || round(random()*2000000) | |'", "tags": ["python", "golang"]}')::jsonb from (select * from generate_series(1.1000000)) as tmp;

Nickname tags ["python"] insert 100W nickname tags ["python"
insert into account select uuid_generate_v1(), ('{"nickname": "nn-' || round(random()*2000000) | |'", "tags": ["python"]}')::jsonb from (select * from generate_series(1.1000000)) as tmp;
Copy the code

Test the query

  • EXPLAIN: Displays the execution plan generated by the PostgreSQL scheduler for the supplied statements.
  • ANALYZE: Collects statistics about the contents of tables in the database.
-- The query of the number of data items with Avatar key in content count(*) is not a good test statement, even if there is an index, it can only play the role of filtering, if the result set is relatively large, Explain Analyze SELECT count(*) from account WHERE content:: jSONb? 'avatar'; QUERY PLAN ---------------------------------------------------------------------------------------- Finalize Aggregate (cost = 29280.40.. 29280.41 Rows =1 width=8) (actual time=170.366.. 170.366 rows=1 loops=1) -> Gather (cost=29280.19.. 29280.40 rows=2 width=8) (actual time=170.119.. 174.451 rows=3 loops=1) Workers Planned: 2 Workers Launched: 2 -> Partial Aggregate (cost=28280.19.. 28280.20 Rows =1 width=8) (actual time=166.034.. 166.034 Rows =1 loops=3) -> Parallel Seq Scan on account (cost=0.00.. 28278.83 Rows =542 width=0) (actual time=0.022.. 161.937 rows=33333 loops=3) Filter: (content? 'Avatar '::text) Rows Removed by Filter: 400000 Planning Time: 0.048ms Execution Time: Explain analyze SELECT count(*) from account where content:: jSONb? 'avatar' = false; QUERY PLAN ---------------------------------------------------------------------------------------- Finalize Aggregate (cost = 30631.86.. 30631.87 rows=1 width=8) (actual time=207.770.. 207.77rows =1 loops=1) -> Gather (cost=30631.65.. 30631.86 rows=2 width=8) (actual time=207.681.. 212.357 rows=3 loops=1) Workers Planned: 2 Workers Launched: 2 -> Partial Aggregate (cost=29631.65.. 29631.66 rows=1 width=8) (actual time=203.565.. 203.565 rows=1 loops=3) -> Parallel Scan on account (cost=0.00.. 28278.83 Rows =541125 width=0) (actual time=0.050.. 163.629 Rows =400000 loops=3) Filter: (NOT (content? 'Avatar '::text)) Rows Removed by Filter: 33333 Planning Time: 0.050 ms Execution Time: 212.393 msCopy the code
Explain analyze SELECT * from account WHERE content@>'{"nickname": "nn-194318"}'; QUERY PLAN ---------------------------------------------------------------------------------------- Gather (cost = 1000.00.. 29408.83 rows=1300 width=100) (actual time=0.159.. 206.990 rows=1 loops=1) Workers Planned: 2 Workers Launched: 2 -> Parallel Seq Scan on account (cost=0.00.. 28278.83 rows=542 width=100) (actual time=130.867.. 198.081 rows=0 loops=3) Filter: (content @> '{"nickname": "nn-194318"}'::jsonb) row Removed by Filter: 433333 Planning Time: 0.04ms Execution Time: Explain Analyze select * from account where b5b3ed06-7d35-11e9-b3eA-00909e9dab1d '=' b5b3ed06-7d35-11e9-b3eA-00909e9dab1d '=' b5b3ed06-7d35-11e9-b3eA-00909e9dab1d id='b5b3ed06-7d35-11e9-b3ea-00909e9dab1d'; QUERY PLAN ---------------------------------------------------------------------------------------- Index Scan using Account_pkey on account (cost = 0.43.. 8.45 rows=1 width=100) (actual time=0.912.. 0.914 rows=1 loops=1) Index Cond: (id = 'b5b3ed06-7d35-11e9-b3EA-00909e9dab1d ':: uUID) Planning Time: Execution Time: 0.931msCopy the code

The main difference between the two statements is that the primary key query used an index, while the Content Nickname query had no index. Next, test the query speed when using an index.

The index

Introduction to GIN Index

The GIN index, which is most commonly used by JSONB, can be used to efficiently search for keys or key-value pairs that are present in a large number of JSONB documents (data).

The GIN(Generalized Inverted Index) is an Index structure that stores a set of pairs (keys, Posting List), where key is a key value and Posting list is a set of positions where keys have occurred. For example, (‘ hello’, ’14:2 23:4′) indicates that Hello occurs at 14:2 and 23:4, which in PG is actually the tid of the tuple (line number, including the data block ID (32bit) and item point(16 bit)).

Each attribute in the table may be resolved into multiple keys when indexing, so the tid of the same tuple may appear in the Posting list of multiple keys.

With this index structure, tuples containing specified keywords can be quickly found. Therefore, the GIN index is especially suitable for multi-valued element search, such as full-text search and array element search. The GIN index module of PG was originally developed to support full-text search.

Does jSONB’s default GIN operator class support the existence operator using the top-level key? ,? & and? The | operator and path/value exists operator @ > query.

Create default index
CREATE INDEX idxgin ON api USING GIN (jdoc);
Copy the code

The non-default GIN operator class jsonb_PATH_OPS only supports the index @> operator.

Create index for the specified path
CREATE INDEX idxginp ON api USING GIN (jdoc jsonb_path_ops);
-- create index ix_account_content_nickname_gin on account using gin (content, (content->'nickname'));
-- create index ix_account_content_tags_gin on account using gin (content, (content->'nickname'));
-- create index ix_account_content_tags_gin on account using gin ((content->'tags'));
Copy the code

Multiple index support

PostgreSQL has an open indexing interface that allows PG to support very rich indexing methods, For example, btree, Hash, gin, gist, SP-Gist, brin, Bloom, rum, Zombodb, bitmap (Greenplum Extend). Select a different index.

Query optimization

Create default index

Create a simple index
create index ix_account_content on account USING GIN (content);
Copy the code

Queries such as the following can now use this index:

Explain analyze SELECT count(*) from account where content:: jSONb? 'avatar'; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------ -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- Aggregate (cost = 4180.49.. 4180.50 rows=1 width=8) (actual time=43.462.. 43.462 rows=1 loops=1) -> Bitmap Heap Scan on account (cost=30.07.. 4177.24 Rows =1300 width=0) (actual time=8.362.. 36.048 rows=100000 loops=1) 'Avatar '::text) Heap Blocks: exact=2032 -> Bitmap Index Scan on ix_account_content (cost=0.00.. 29.75 rows=1300 width=0) (actual time=8.125.. 8.125 rows=100000 loops=1) Index Cond: (content? 'avatar'::text) Planning Time: 0.078ms Execution Time: 43.503msCopy the code

This is three times faster than when no index was added.

Explain analyze SELECT * from account WHERE content@>'{"nickname": "nn-194318"}'; QUERY PLAN ---------------------------------------------------------------------------------------- Bitmap Heap Scan on The account (cost = 46.08.. 4193.24 rows=1300 width=100) (actual time=0.097.. 0.097 rows=1 loops=1) content-@ > '{"nickname": "nn-194318"}'::jsonb) Heap block: Exact =1 -> Bitmap Index Scan on ix_account_content (cost=0.00.. 45.75 rows=1300 width=0) (actual time=0.091.. 0.091 rows=1 loops=1) Index Cond: (content @> '{"nickname": "nn-194318"}'::jsonb) Planning Time: 0.075ms Execution Time: 0.132msCopy the code

This query is even more efficient than using primary keys.

However, the following queries do not use indexes:

Explain analyze SELECT count(*) from account where content:: jSONb? 'avatar' = false; QUERY PLAN ---------------------------------------------------------------------------------------- Finalize Aggregate (cost = 30631.86.. 30631.87 rows=1 width=8) (actual time=207.641.. 207.641 rows=1 loops=1) -> Gather (cost=30631.65.. 30631.86 Rows =2 width=8) (actual time=207.510.. 211.062 rows=3 loops=1) Workers Planned: 2 Workers Launched: 2 -> Partial Aggregate (cost=29631.65.. 29631.66 rows=1 width=8) (actual time=203.739.. 203.739 Rows =1 loops=3) -> Parallel Scan on account (cost=0.00.. 28278.83 Rows =541125 width=0) (actual time=0.024.. 163.444 rows=400000 loops=3) Filter: (NOT (content? 'Avatar '::text)) Rows Removed by Filter: 33333 Planning Time: 0.068 ms Execution Time: 211.097 msCopy the code

The index also cannot be used for queries such as the following, because although the operator? Is indexable, but it cannot be directly applied to the indexed column content:

explain analyze select count(1) from account where content -> 'tags' ? 'c'; QUERY PLAN ---------------------------------------------------------------------------------------- Finalize Aggregate (cost = 30634.57.. 30634.58 rows=1 width=8) (actual time=184.864.. 184.864 rows=1 loops=1) -> Gather (cost=30634.35.. 30634.56 rows=2 width=8) (actual time=184.754.. 189.652 rows=3 loops=1) Workers Planned: 2 Workers Launched: 2 -> Partial Aggregate (cost=29634.35.. 29634.36 rows=1 width=8) (actual time=180.755.. 180.755 rows=1 loops=3) -> Parallel Scan on account (cost=0.00.. 29633.00 rows=542 width=0) (actual time=0.022.. 177.051 Rows =33333 loops=3) Filter: ((content -> tags ::text)? 'C '::text) Rows Removed by Filter: 400000 Planning Time: 0.074 ms Execution Time: 189.716 msCopy the code

Using expression indexes

Create a path index
create index ix_account_content_tags on account USING GIN ((content->'tags'));
Copy the code
Explain Analyze SELECT count(1) from account where content -> 'tags'? 'c'; QUERY PLAN ---------------------------------------------------------------------------------------- Aggregate (cost = 4631.74.. 4631.75 rows=1 width=8) (actual time=49.274.. 49.275 rows=1 loops=1) -> Bitmap Heap Scan on account (cost=478.07.. 4628.49 rows=1300 width=0) (actual time=8.655.. 42.074 rows=100000 loops=1) Recheck Cond: ((content -> 'tags'::text)? 'c'::text) Heap Blocks: exact=2032 -> Bitmap Index Scan on ix_account_content_tags (cost=0.00.. 477.75 rows=1300 width=0) (actual time=8.417.. 8.417 rows=100000 loops=1) Index Cond: ((content -> 'tags'::text)? 'c'::text) Planning Time: 0.216ms Execution Time: 49.309msCopy the code

Now, WHERE clause content -> ‘tags’? Will ‘c’ be recognized as indexable? Application on index expression content -> ‘tags’.

You can also include queries, such as:

-- look for the number of tags containing the array element "c"
select count(1) from account where content@ >'{"tags": ["c"]}';
Copy the code

A simple GIN index (the default index) on the Content column supports index queries. Whereas the index will store copies of each key and value in the Content column, the expression index will only store data found under the tags key.

Although the simple index approach is more flexible (because it supports queries about arbitrary keys), the directed expression index is smaller and searches faster than the simple index. Although the jsonB_PATH_OPS operator class only supports queries with the @> operator, it has a more objective performance advantage over the default operator class jsonb_OPS. A JSONB_PATH_OPS index is also generally much smaller than a JSONB_OPS index on the same data, and search specificity is better, especially if the query contains keys that occur frequently in the data. As a result, search operations on it generally perform better than searches using the default operator classes.

conclusion

  • PG has two JSON data types:json 和 jsonb, jSONB has better performance than JSON, and JSONB supports indexing.
  • Jsonb handles writing when it writes, which is relatively slow, while JSON retains the raw data (including unwanted whitespace)
  • A good way to optimize jSONB queries is to add GIN indexes
    • Simple indexes are more flexible than path indexes, but take up more space
    • Path indexes are more efficient and take up less space than simple indexes

Refer to the link

  • RFC 7159 The JavaScript Object Notation (JSON) Data Interchange Format
  • PostgreSQL documents: The type is JSON
  • JSON functions and operators
  • How do I modify fields inside the new PostgreSQL JSON datatype?
  • This section describes the principles and application scenarios of the nine PostgreSQL indexes
  • PostgreSQL GIN index implementation principle
  • PostgreSQL internals: JSONB type and its indexes
  • Inverted index

Finally, thank my girlfriend for her support and tolerance, more than ❤️

Around the male can also enter the keywords for historical article: male number & small program | | concurrent design mode & coroutines