Add, delete, modify and check PostgreSQL jsonb array

Sometimes we need to use a structured database such as PostgreSQL to store some unstructured data. PostgreSQL happens to provide a data type of json. Here we briefly introduce some common operations using jsonb.

PostgreSQL provides two types of json, json and jsonb. The main difference between the two is that json query is relatively slow and insertion will be faster, while jsonb is the opposite, query efficiency will be higher, and insertion will be slower.

Let's enter our topic today

Table Structure

create table purchase_order
(
    id                     serial not null primary key,
    tag                    jsonb
)

The data is also very simple

INSERT INTO plutus.purchase_order (id, tag) VALUES (4787, '[{"uid": 1, "name": "标签名1", "add_time": "2021-05-29 17:00:00"}, {"uid": 2, "name": "标签名2", "add_time": "2021-05-29 17:00:00"}]');

The point is tag

[{
	"uid": 1,
	"name": "标签名1",
	"add_time": "2021-05-29 17:00:00"
}, {
	"uid": 2,
	"name": "标签名2",
	"add_time": "2021-05-29 17:00:00"
}]

operating

Here to implement some simple operations

Condition query

We query the tag with id = 4787 and uid = 1 in json

-- 嵌套子查询
select * from (
select jsonb_array_elements(tag) as tt from purchase_order where id =  4787) a
where  tt -> 'uid' = '1';

-- 或者这种方式
SELECT
    id,r
FROM
    purchase_order s, jsonb_array_elements(s.tag) r
WHERE
    s.id =  4787  and r->>'uid' = '2' ;

Query result

Insert picture description here


If you only query the result that contains uid = 3 in the json, you can query like this

SELECT tag FROM purchase_order
WHERE id =  4787 and tag @> '[{"uid": 3}]';

Add

Adding is relatively simple, we are adding an object to the original json.
The original json object is like this

[{
	"uid": 1,
	"name": "标签名1",
	"add_time": "2021-05-29 17:00:00"
}, {
	"uid": 2,
	"name": "标签名2",
	"add_time": "2021-05-29 17:00:00"
}]

Execute the following statement

UPDATE purchase_order SET tag = tag || '[{
	"uid": 3,
	"name": "标签名3",
	"add_time": "2021-05-29 17:00:00"
}]' where  id = 4787;

Query again

[{
	"uid": 1,
	"name": "标签名1",
	"add_time": "2021-05-29 17:00:00"
}, {
	"uid": 2,
	"name": "标签名2",
	"add_time": "2021-05-29 17:00:00"
}, {
	"uid": 3,
	"name": "标签名3",
	"add_time": "2021-05-29 17:00:00"
}]

delete

If I want to delete the value of uid 3 above, I can use the following sql

UPDATE purchase_order
       SET tag = t.js_new
      FROM
          (
           SELECT jsonb_agg( (tag ->> ( idx-1 )::int)::jsonb ) AS js_new
             FROM purchase_order
            CROSS JOIN jsonb_array_elements(tag)
             WITH ORDINALITY arr(j,idx)
            WHERE j->>'uid' NOT IN ('3')
          ) t;

Query again

Insert picture description here

modify

We are now trying to id = 4787
name = '标签名1'
modify isname = '标签new'

sql is as follows

UPDATE purchase_order AS g
   SET tag = REPLACE(tag::text, '"name": "标签名1"','"name": "标签new"')::json
 WHERE g.tag IN ( SELECT g.tag
                     FROM purchase_order AS g
                    CROSS JOIN jsonb_array_elements(g.tag) AS j
                    WHERE id = 4787 and  j ->>'uid' = '1' )

to sum up

In fact, there are many other ways to achieve this, but in general, modification and deletion are not particularly convenient and easy to maintain. It is recommended that when modifying and deleting, you can directly use it as a string to update in full, so that it is easier to maintain.

Also accompanied by an official document connected to the operation of jsonb we all go to access
links
pay attention to select the appropriate version of the document oh