Shulou(Shulou.com)05/31 Report--

How to use the jsonb data type in PostgreSQL, for this question, this article introduces the corresponding analysis and solution in detail, hoping to help more partners who want to solve this problem to find a more simple and easy way.

PostgreSQL 9.4 is loading a new feature called jsonb, a new type of data that can store JSON data that supports GIN indexes. In other words, this feature, and most importantly in the upcoming update, if it doesn't matter, put Postgres in the recommended location for the file-based database system.

Since 9.2, an integrated JSON data type has existed with a full set of functions (such as data generation and data deconstruction), as well as 9.3 new operators. When using the JSON data type, the data is stored as an identical copy, the function still operates on top of it, and a reanalysis of the background operation is required.

The lesson is that the JSONB data type is stored in a degraded binary format, so inserting this data is more efficient than JSON because there is no need for reanalysis in the background, so it runs faster and takes into account GIN indexes. It is for this last reason that we actually recommend that readers use jsonb instead of json for programming (of course, you can also use json as needed). Keep in mind that jsonb uses the same operators and features, and readers can read my previous post to enlighten you (or simply read the Postgres file).

Now let's take a look at how JSONB works and compare it with JSON. The test data used is 8.6 million geobase type data, about 1.1g in size, including city name, country code (you can see the complete list here) and many other fields. First, the data is stored in a new table in the database through underlying replication (raw copy), then the table is converted into JSON/JSONB through a set of tables with a fill factor of 100, and then see how much space each takes up.

= # COPY geodata FROM'$HOME/Downloads/allCountries.txt';COPY 8647839 CREATE TABLE geodata_jsonb (data jsonb) with (fillfactor=100); CREATE TABLE=# CREATE TABLE geodata_json (data json) with (fillfactor=100); CREATE TABLE=#\ timingTiming is on.=# INSERT INTO geodata_json SELECT row_to_json (geodata) FROM geodata;INSERT 0 8647839Time: 287158.457 ms=# INSERT INTO geodata_jsonb SELECT row_to_json (geodata):: jsonb FROM geodata;INSERT 0 8647839Time: 425825.967 ms

It takes a little longer to generate JSONB data. Is there any difference in size?

= # SELECT pg_size_pretty (pg_relation_size ('geodata_json'::regclass)) AS json, pg_size_pretty (pg_relation_size (' geodata_jsonb'::regclass)) AS jsonb; json | jsonb-+-3274 MB | 3816 MB (1 row)

Indexing on JSON data starts with version 9.3, such as using operators (note that'- > >'is adopted because it returns text; and indexes use different keywords depending on the query)

= # CREATE INDEX geodata_index ON geodata_json ((data- > > 'country_code'), (data- > >' asciiname')); CREATE INDEX=# SELECT pg_size_pretty (pg_relation_size ('geodata_index'::regclass)) AS json_index Json_index-310 MB (1 row) = # SELECT (data- > > 'population'):: int as population, data- >' latitude' as latitude, data- > 'longitude' as longitude FROM geodata_json WHERE data- > >' country_code' = 'JP' AND data- > >' asciiname' = 'Tokyo' AND (data- > >' population'):: int! = 0 Population | latitude | longitude-+-8336599 | 35.6895 | 139.69171 (1 row) = #-- Explain of previous query QUERY PLAN-- -Bitmap Heap Scan on geodata_json (cost=6.78..865.24 rows=215 width=32) Recheck Cond: ( Data-> > 'country_code'::text) =' JP'::text) AND ((data-> > 'asciiname'::text) =' Tokyo'::text)) Filter: ((data-> > 'population'::text)):: integer 0)-> Bitmap Index Scan on geodata_index (cost=0.00..6.72 rows=216 width=0) Index Cond: ((data-> >' country_code'::text) = 'JP'::text) AND ((data) -> > 'asciiname'::text) =' Tokyo'::text)) Planning time: 0.172 ms (6 rows)

In this example, the planner can use bitmap index scanning, while using the previously generated index.

Now, a new feature of JSONB is to check the capacity of data with the operator @ >, which can be indexed with GIN, and this operator data also includes?,? | and? & (to check whether a given keyword exists). GIN indexes work on two types of operators:

The default operator class, the four previously listed

Jsonb_hash_ops, which only supports @ >, but performs well when searching for data and takes up less disk space

Here's how it works:

= # CREATE INDEX geodata_gin ON geodata_jsonb USING GIN (data jsonb_hash_ops); CREATE INDEX=# SELECT (data- > > 'population'):: int as population, data- >' latitude' as latitude, data- > 'longitude' as longitude FROM geodata_jsonb WHERE data @ >' {"country_code": "JP", "asciiname": "Tokyo"} 'AND (data- > >' population'):: int! = 0 Population | latitude | longitude-+-+-8336599 | 35.6895 | 139.69171 (1 row) = # SELECT pg_size_pretty (pg_relation_size ('geodata_gin'::regclass)) AS jsonb_gin Jsonb_gin- 1519 MB (1 row) = #-EXPLAIN of previous query QUERY PLAN- -Bitmap Heap Scan on geodata_jsonb (cost=131.01..31317.76 rows=8605 width=418) Recheck Cond: (data @ >'{"asciiname": "Tokyo" "country_code": "JP"}':: jsonb) Filter: ((data-> > 'population'::text)):: integer 0)-> Bitmap Index Scan on geodata_gin (cost=0.00..128.86 rows=8648 width=0) Index Cond: (data @ >' {"asciiname": "Tokyo", "country_code": "JP"}':: jsonb) Planning time: 0.134 ms

Depending on the requirements of the application, you may want to use indexes with low space consumption, such as the type of index built by BTree on JSON data; the GIN index has more advantages because it covers all JSON fields and checks the capacity

This is the answer to the question about how to use the jsonb data type in PostgreSQL. I hope the above content can be of some help to you. If you still have a lot of doubts to be solved, you can follow the industry information channel to learn more about it.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.