Product Documentation

Appearance

JSON_ARRAY_STR ​

Compose a JSON array from the specified inputs and return the array as a VARCHAR column.

json_array_str(input [, ...])

The data types of the individual inputs may vary. In turn, the length of the returned VARCHAR column varies, depending on its inputs. If necessary, you can use an explicit cast to modify the return type.

Single-quoted input strings are double-quoted in the output. Double-quoted input strings are interpreted as column names.

Examples ​

For example, compose an array from two columns in the match table:

premdb=# select matchday, htid, atid, htscore, ftscore, json_array_str(htscore, ftscore) score_array 
from match where seasonid=21 and htid=3 
order by 1;
     matchday       | htid | atid | htscore | ftscore |  score_array   
---------------------+------+------+---------+---------+----------------
 2012-08-25 00:00:00 |    3 |   67 | 0-3     | 1-3     | ["0-3", "1-3"]
 2012-09-15 00:00:00 |    3 |   89 | 1-0     | 2-0     | ["1-0", "2-0"]
 2012-09-30 00:00:00 |    3 |   93 | 0-0     | 1-1     | ["0-0", "1-1"]
 2012-10-27 00:00:00 |    3 |   78 | 1-0     | 1-1     | ["1-0", "1-1"]
 2012-11-10 00:00:00 |    3 |   75 | 1-0     | 2-3     | ["1-0", "2-3"]
 2012-11-24 00:00:00 |    3 |   51 | 0-0     | 0-0     | ["0-0", "0-0"]
 2012-11-27 00:00:00 |    3 |   83 | 0-0     | 1-0     | ["0-0", "1-0"]
 2012-12-08 00:00:00 |    3 |   87 | 0-0     | 0-0     | ["0-0", "0-0"]
 2012-12-26 00:00:00 |    3 |   91 | 0-0     | 0-4     | ["0-0", "0-4"]
 2012-12-29 00:00:00 |    3 |   95 | 0-1     | 0-3     | ["0-1", "0-3"]
...

Compose an array that consists of a column and the result of an aggregate function:

premdb=# select json_array_str(ftscore, count(*)) 
from match group by ftscore order by 1;
   ?column?    
---------------
 ["0-0", 738]
 ["0-1", 633]
 ["0-2", 371]
 ["0-3", 162]
 ["0-4", 74]
 ["0-5", 19]
 ["0-6", 7]
 ["1-0", 943]
 ["1-1", 1024]
 ["1-2", 513]

In this example, the array consists of four inputs, based on a join of two tables:

premdb=# select json_array_str(season.seasonid,htid,atid,matchday::date) season22 
from season, match where match.seasonid=season.seasonid and season.seasonid=22 
order by season.seasonid;
         season22          
----------------------------
 [22, 2, 52, "2013-08-17"]
 [22, 2, 61, "2014-01-01"]
 [22, 2, 63, "2013-12-23"]
 [22, 2, 65, "2014-02-02"]
 [22, 2, 67, "2013-12-08"]
 [22, 2, 68, "2014-01-18"]
 [22, 2, 69, "2013-12-04"]
...

Parent topic:JSON Functions