Postgresql – Array stringifies to using `{}` in Postgres output

arraypostgresqlselect

I have an array column but when I output it using SQL SELECT, it looks like

{{"a":"b","c":"d"},{"e":"f","g":"h"}}

But why does it start and end with {{ and }}? Shouldn't it be [{ and }] for arrays? Inside output of JSONB columns, arrays look as expected (square brackets, not curly braces, surrounding the array values).

Have I done something wrong, or is the output above truly an array of objects?

Best Answer

From the docs in Array Input and Output Syntax

The external text representation of an array value consists of items that are interpreted according to the I/O conversion rules for the array's element type, plus decoration that indicates the array structure. The decoration consists of curly braces ({ and }) around the array value plus delimiter characters between adjacent items. The delimiter character is usually a comma (,) but can be something else: it is determined by the typdelim setting for the array's element type. Among the standard data types provided in the PostgreSQL distribution, all use a comma, except for type box, which uses a semicolon (;). In a multidimensional array, each dimension (row, plane, cube, etc.) gets its own level of curly braces, and delimiters must be written between adjacent curly-braced entities of the same level.

Not sure if stingification of ARRAY is in the spec or not, but for PostgreSQL it's perfectly normal to stingify it with {}. Note, this is not symmetrical with the literal syntax to create an array. 

SELECT ARRAY[1,2,3];
  array  
---------
 {1,2,3}

But you can see that it's valid and to be expected because you can cast to an array explicitly.

SELECT '{1,2,3}'::int[];
  int4   
---------
 {1,2,3}

If in your case you have two jsonb in the ARRAY, it'll get stringified as such

SELECT ARRAY[
  $${"a":"b","c":"d"}$$::jsonb,
  $${"e":"f","g":"h"}$$::jsonb
];
                              array                              
-----------------------------------------------------------------
 {"{\"a\": \"b\", \"c\": \"d\"}","{\"e\": \"f\", \"g\": \"h\"}"}

Note that a two JSON-objects in a PostgreSQL array, is different from two objects in a JSON array.

SELECT $$[{"a":"b","c":"d"},{"e":"f","g":"h"}]$$::jsonb;
                    jsonb                     
----------------------------------------------
 [{"a": "b", "c": "d"}, {"e": "f", "g": "h"}]
Related Question