Shulou(Shulou.com)06/01 Report--

Today, I will talk to you about how to deal with the JSON of PostgreSQL. Many people may not know much about it. In order to make you understand better, the editor has summarized the following contents for you. I hope you can get something according to this article.

First of all, POSTGRESQL supports two JSON formats, JSON and JSONB. There is already a scale of support for JSON in PG 9.x (BSON format is supported in MONGODB). If you have a high probability of using a format like JSONB in postgresql, this format has the following advantages

1 the processing speed is fast

2 you can add an index

Of course, there are some things that are limited, not to mention here, but in addition to the learning process, I found that if you can get a little sweetness in the learning process, then you will be interested in subsequent learning, and it is likely that you will learn faster and faster.

So let's first do a little practice to increase our interest.

We set up a table, which, in terms of MONGODB, should be called collection, and then we insert a document with the format jsonb, but if you understand it in MONGODB, you can find something a little different.

As you can see, it is not difficult to display data in POSTGRESQL.

Then we create an index, people familiar with PG know that his fuzzy query is strong, the establishment of the following index is also quite a bit of flavor.

Explain analyze SELECT * FROM json_test WHERE info @ >'{"name": "Calor"}'

There are also some special JSONB query methods in the query, but this query method is much more friendly to people who are familiar with SQL than those who are familiar with MONGODB for the first time.

Here we can summarize that JSON in PostgreSQL can be stored using JSONB, and most of the time creating an index of GIN can satisfy most of the queries. If you have used MONGODB, you can know that MONGODB itself is stored in a B+ tree to store indexes, and specific queries also have to cooperate with specific indexes.

Let's continue testing to see if POSTGRESQL can HOLD all queries with one index.

Let's create a new collection.

Create table test_json_2 (info jsonb)

Insert data

Insert into test_json_2 select ('{"name": "nn-' | | round (random () * 20000000) | |'", "ajon": "address", "tags": ["system", "database", "langrage"]}'): jsonb from (select * from generate_series) as temp

A 100000-line collection is generated, according to the prescription, after creating the index, directly query, the result is obviously able to go to the index.

And some of the cases mentioned on the Internet that cannot be indexed can also be started in this version of PG11.

Of course, if the data you are counting is the whole table, or most of the data, you still have to scan the whole table.

Here's a rough look at POSTGRESQL's support for JOSN, which is friendly to some databases and in line with the way humans quickly understand and get started.

Of course, in the face of the strong processing power of the professional document database MONGODB, no other database can compete, because that is his rice bowl, if it is not very complex (that is, the kind of nesting, and the array is going to explode) JSON, or you do not have the support of professional MONGODB, you want to use JSON or documents in the database and do not want to be disappointed. POSTGRESQL is a choice. It's not bad.

After reading the above, do you have any further understanding of how to deal with the JSON of PostgreSQL? If you want to know more knowledge or related content, please follow the industry information channel, thank you for your support.

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.