Product Documentation

Appearance

JSON_OBJECT_STR ​

Compose a JSON object from one or more key-value pairs and return the object as a VARCHAR column.

JSON_OBJECT_STR(key, value [, ...])

Each input may be a string literal, a numeric constant, a scalar function, a scalar subquery, or a table column.

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.

The input list must contain an even number of elements. All keys must specify a value. NULL is allowed as a value, but not as a key. Duplicate keys with the same name are allowed.

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

Examples ​

For example, the following query returns two key-value pairs from each season table row:

premdb=# select json_object_str('Season Number', seasonid, 'Season Years', season_name) "25 Seasons" 
from season order by seasonid;
                     25 Seasons                      
------------------------------------------------------
 {"Season Number" : 1, "Season Years" : "1992-1993"}
 {"Season Number" : 2, "Season Years" : "1993-1994"}
 {"Season Number" : 3, "Season Years" : "1994-1995"}
 {"Season Number" : 4, "Season Years" : "1995-1996"}
 {"Season Number" : 5, "Season Years" : "1996-1997"}
...

The following example shows how to return a whole table (all of its column names and their respective values) as a set of JSON objects, one per row:

premdb=# select json_object_str('seasonid',seasonid,'season_name', season_name, 'numteams', numteams, 'winners', winners) seasontable from season;
                                          seasontable                                            
--------------------------------------------------------------------------------------------------
 {"seasonid" : 1, "season_name" : "1992-1993", "numteams" : 22, "winners" : "Manchester United"}
 {"seasonid" : 2, "season_name" : "1993-1994", "numteams" : 22, "winners" : "Manchester United"}
 {"seasonid" : 3, "season_name" : "1994-1995", "numteams" : 22, "winners" : "Blackburn Rovers"}
 {"seasonid" : 4, "season_name" : "1995-1996", "numteams" : 20, "winners" : "Manchester United"}
 {"seasonid" : 5, "season_name" : "1996-1997", "numteams" : 20, "winners" : "Manchester United"}
 {"seasonid" : 6, "season_name" : "1997-1998", "numteams" : 20, "winners" : "Arsenal"}
 {"seasonid" : 7, "season_name" : "1998-1999", "numteams" : 20, "winners" : "Manchester United"}
 {"seasonid" : 8, "season_name" : "1999-2000", "numteams" : 20, "winners" : "Manchester United"}
 {"seasonid" : 9, "season_name" : "2000-2001", "numteams" : 20, "winners" : "Manchester United"}
 {"seasonid" : 10, "season_name" : "2001-2002", "numteams" : 20, "winners" : "Arsenal"}
 {"seasonid" : 11, "season_name" : "2002-2003", "numteams" : 20, "winners" : "Manchester United"}
 {"seasonid" : 12, "season_name" : "2003-2004", "numteams" : 20, "winners" : "Arsenal"}
 {"seasonid" : 13, "season_name" : "2004-2005", "numteams" : 20, "winners" : "Chelsea"}
 {"seasonid" : 14, "season_name" : "2005-2006", "numteams" : 20, "winners" : "Chelsea"}
 {"seasonid" : 15, "season_name" : "2006-2007", "numteams" : 20, "winners" : "Manchester United"}
 {"seasonid" : 16, "season_name" : "2007-2008", "numteams" : 20, "winners" : "Manchester United"}
 {"seasonid" : 17, "season_name" : "2008-2009", "numteams" : 20, "winners" : "Manchester United"}
 {"seasonid" : 18, "season_name" : "2009-2010", "numteams" : 20, "winners" : "Chelsea"}
 {"seasonid" : 19, "season_name" : "2010-2011", "numteams" : 20, "winners" : "Manchester United"}
 {"seasonid" : 20, "season_name" : "2011-2012", "numteams" : 20, "winners" : "Manchester City"}
 {"seasonid" : 21, "season_name" : "2012-2013", "numteams" : 20, "winners" : "Manchester United"}
 {"seasonid" : 22, "season_name" : "2013-2014", "numteams" : 20, "winners" : "Manchester City"}
 {"seasonid" : 23, "season_name" : "2014-2015", "numteams" : 20, "winners" : "Chelsea"}
 {"seasonid" : 24, "season_name" : "2015-2016", "numteams" : 20, "winners" : "Leicester City"}
 {"seasonid" : 25, "season_name" : "2016-2017", "numteams" : 20, "winners" : null}
(25 rows)

The following example returns an error because the 7th parameter (param=>7 in the DETAIL message) contains a NULL value. The 7th parameter is the winners column in the season table.

premdb=# select json_object_str(seasonid,1,season_name,2,numteams,3,winners,4) from season;
ERROR:  The key for this json object is invalid / null / not a string
DETAIL:   [param=>7]

Parent topic:JSON Functions