Array stringifies to using `{}` in Postgres output

Question

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?

• user1822: The default text representation of an array is with the curly braces: postgresql.org/docs/current/static/arrays.html#ARRAYS-IO

Answer 1

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"}]

• user779159: Maybe this is what's confusing me, what's the difference between a Postgres array and a JSON array?
• user779159: Btw what's the $$ syntax you used in your answer? I haven't seen it before.
• Evan Carroll: The $$ is a special quoting construct called "dollar quoting" for literals. It allows you to quote ' easily.