PostgreSQL Multi-meaning Array Retrieval and Condition Filtering

Background

PostgreSQL's array type is widely used across portrait and tag systems. In some business reconstruction, the definition of the array content often contains multiple meanings. For example, it contains both, the tag as well as the attributes of the tag such as the tag value, weighted value, and time.

The challenge that arises here is how to efficiently search tags and filter records that match the tag's weighted value?

An Example: PostgreSQL Multi-meaning Array

Let's consider the following example to understand how to search for tags and filter records that match the tag's weighted value:

Step 1. Create a table as shown below.

create table tbl(id int, info text[]);

Step 2. Now write the test data.

insert into tbl values (1, array['a:100', 'b:10']);  
  
insert into tbl values (2, array['a:15', 'b:20', 'c:99']);  
  
insert into tbl values (3, array['c:78', 'b:100']);  
  
postgres=# select * from tbl;  
 id |       info         
----+------------------  
  1 | {a:100,b:10}  
  2 | {a:15,b:20,c:99}  
  3 | {c:78,b:100}  
(3 rows)

Step 3. Create UDF1 and extract the tag value to query by using regular matching.

create or replace function get_label(text[]) returns text[] as $$  
  select array(select substring(unnest($1), '(.*):'));  
$$ language sql strict immutable;  
  
postgres=# select get_label(info) from tbl;  
 get_label   
-----------  
 {a,b}  
 {a,b,c}  
 {c,b}  
(3 rows)

Step 4. Next, you need to create a UDF1 index as shown below.

create index idx_tbl1 on tbl using gin (get_label(info));  
  
postgres=# explain select * from tbl where get_label(info) @> array['a'];  
                              QUERY PLAN                                 
-----------------------------------------------------------------------  
 Bitmap Heap Scan on tbl  (cost=2.40..3.86 rows=1 width=36)  
   Recheck Cond: (get_label(info) @> '{a}'::text[])  
   ->  Bitmap Index Scan on idx_tbl1  (cost=0.00..2.40 rows=1 width=0)  
         Index Cond: (get_label(info) @> '{a}'::text[])  
(4 rows)

Step 5. Create UDF2 and extract the weighted value of the specified tag. Here, you may use regular matching, calculate the array subscript, add an array element based on the position, and perform other operations.

create or replace function get_weight(text[], text) returns text as $$  
  select substring($1[array_position(get_label($1), $2)], ':(.*)');  
$$ language sql strict immutable;  
  
  
postgres=# select info, get_weight(info, 'a') from tbl;  
       info       | get_weight   
------------------+------------  
 {a:100,b:10}     | 100  
 {a:15,b:20,c:99} | 15  
 {c:78,b:100}     |   
(3 rows)

Step 6. Use the following SQL statement for the query. Now, query the records containing tag A with a weighted value greater than 20.

postgres=# select * from tbl where get_label(info) @> array['a'] and get_weight(info, 'a')::float8 >20;  
 id |     info       
----+--------------  
  1 | {a:100,b:10}  
(1 row)  
  
postgres=# explain select * from tbl where get_label(info) @> array['a'] and get_weight(info, 'a')::float8 >20;  
                                      QUERY PLAN                                        
--------------------------------------------------------------------------------------  
 Bitmap Heap Scan on tbl  (cost=2.40..4.12 rows=1 width=36)  
   Recheck Cond: (get_label(info) @> '{a}'::text[])  
   Filter: ((get_weight(info, 'a'::text))::double precision > '20'::double precision)  
   ->  Bitmap Index Scan on idx_tbl1  (cost=0.00..2.40 rows=1 width=0)  
         Index Cond: (get_label(info) @> '{a}'::text[])  
(5 rows)

There you go! You have have retrieved the records that match the tag's weighted value.
Isn't UDF awesome!

How to Modify an Array and Append an Array Element Using UPSERT

Follow the steps listed below to modify or append an array element. Refer this document to get an overview of various array operators and functions used in the steps below:

Step 1. Append an array element using the following commands.

array_append(anyarray, anyelement)  
  
array_cat(anyarray, anyarray)  
  
array_fill(anyelement, int[], [, int[]])  
  
array_prepend(anyelement, anyarray)

Step 2. Also, you can use the following commands to modify an element.

array_replace(anyarray, anyelement, anyelement)

Step 3. In case you want to remove an element, refer the following command for the same.

array_remove(anyarray, anyelement)

An Example

Following example illustrates the array modification and appending of an array element:

insert into tbl values (1, ?) on conflict (id) do update set info=func(tbl.info,?);    
create table tbl1(id int primary key, info int[]);  
  
postgres=# insert into tbl1 values (1, array[1,2,3]) on conflict (id) do update set info=array_append(tbl1.info, 100) returning *;   
 id |  info     
----+---------  
  1 | {1,2,3}  
(1 row)  
  
INSERT 0 1  
postgres=# insert into tbl1 values (1, array[1,2,3]) on conflict (id) do update set info=array_append(tbl1.info, 100) returning *;   
 id |    info       
----+-------------  
  1 | {1,2,3,100}  
(1 row)  
  
INSERT 0 1  
postgres=# insert into tbl1 values (1, null) on conflict (id) do update set info=array_append(tbl1.info, 100) returning *;   
 id |      info         
----+-----------------  
  1 | {1,2,3,100,100}  
(1 row)  
  
INSERT 0 1

Build your own PostgreSQL solution on Alibaba Cloud with ApsaraDB for RDS PostgreSQL.

Community

PostgreSQL Multi-meaning Array Retrieval and Condition Filtering

Background

An Example: PostgreSQL Multi-meaning Array

How to Modify an Array and Append an Array Element Using UPSERT

An Example

Read previous post:

Read next post:

digoal

You may also like

Comments

digoal

Related Products

PolarDB for PostgreSQL

Hybrid Cloud Storage

AnalyticDB for PostgreSQL

ApsaraDB RDS for PostgreSQL