This article is half-done without your Comment! *** Please share your thoughts via Comment ***
PostgreSQL 9.4 introduced WITH ORDINALITY for generating a sequence number for each element which are produced by UNNEST().
When we are talking about the string array, internal element numbering is also important for further operation.
I have prepared a small demonstration of this, you can access here.
Create a table with sample data:
|
1 2 3 4 5 6 7 |
CREATE TABLE tbl_TextArray(key varchar(100), tags text[]); INSERT INTO tbl_TextArray(key, tags) VALUES ('first', '{aa,fe,gr,fh}'::text[]), ('sec', '{tr,we,et,ui,er}'::text[]), ('third', '{we,gh,fv,dc,sx}'::text[]); |
UNNEST() WITH ORDINALITY:
|
1 2 3 |
SELECT key, ArrayOrder, tag FROM tbl_TextArray, unnest(tags) WITH ORDINALITY As T (tag, ArrayOrder); |
The Result:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
dbrnd=# SELECT key, ArrayOrder, tag dbrnd-# FROM tbl_TextArray, unnest(tags) dbrnd-# WITH ORDINALITY As T (tag, ArrayOrder); key | arrayorder | tag -------+------------+----- first | 1 | aa first | 2 | fe first | 3 | gr first | 4 | fh sec | 1 | tr sec | 2 | we sec | 3 | et sec | 4 | ui sec | 5 | er third | 1 | we third | 2 | gh third | 3 | fv third | 4 | dc third | 5 | sx (14 rows) |
