how to set type for jsonb[] for play-scala anorm pgsql

Question

I am building a survey application in scala/play framework, and am using postgres9.4 and anorm. I am using jsonb as a datatype in other places but in one location I want to use jsonb[] thinking that this type is an array of jsonb values. My json structure is like the following:

[
    {"guitar":{"passion":3,
               "expertise":5,
               "willingToTeach":false,
               "lookingForOthers":false
              }
     },
     {"soccer":{"passion":3,
                "expertise":3,
                "willingToTeach":true,
                "lookingForOthers":true
                }
     }
]

Here each interest is a json structure. I have been able to add json response values to other columns in pgsql using jsonbas the data type, but when I try to use jsonb[] I get complaints: [PSQLException: Unknown type jsonb[].] In pgadmin3 it literally shows my this exact data type: jsonb[] for the column I am trying to insert into. In my anorm insert code I have tried setting the type:

val pgObject = new PGobject();
pgObject.setType("jsonb")

But then I get this error:

[PSQLException: ERROR: column "passions" is of type jsonb[] but expression is of type jsonb
  Hint: You will need to rewrite or cast the expression.
  Position: 43]

I have tried looking this up but I can't even seem to find what all string values I can use as an argument for pgObject.setType(). I am also unsure how I would go about casting the expression from jsonb to jsonb[] any other way than setting the type using the setType() method.

Any help would be greatly appreciated.

Why would you use jsonb[]? jsonb has a native array format internally. It doesn't make sense to use a PostgreSQL array of jsonb values. — Craig Ringer
basically i want an array of JSONB values. some users may have more interests than others. and I need to append this information to a user every time they add data regarding a specific interest. I could not find too much info regarding JSONB[], but it sounds like an array of JSONB values. I suppose I can contain my JSON in surrounding curly braces {} and try to append this structure each time instead of overwriting it. I will try this suggestion — kinghenry14
but json has arrays. Just provide a single jsonb, where the top level element is a json array containing json objects. — Craig Ringer

Craig Ringer Craig Ringer · Accepted Answer · 2015-08-03T01:40:08

Many frameworks don't support SQL arrays. There's an SQL standard for arrays but most clients have partial support or no support at all for it.

In PostgreSQL type[] is an array of type. So jsonb[] is an array of jsonb. Your client does not appear to understand this - either it doesn't support arrays, or it has special-cased array support on a per-data-type basis.

Luckily, you don't need a SQL array of jsonb, because jsonb stores JSON the JavaScript Serialized Object Notation. JSON defines both objects and arrays. So you can have an array of objects inside the jsonb field. No need for SQL arrays.

This is an SQL array of jsonb values, where each jsonb value is a single object (dictionary):

test=> SELECT ARRAY[
           '{ "x": 1, "y": 2 }',
           '{ "a": 3, "b": 4 }'
        ] :: jsonb[];
                        array                        
-----------------------------------------------------
 {"{ \"x\": 1, \"y\": 2 }","{ \"a\": 3, \"b\": 4 }"}
(1 row)

It has the data type jsonb[]. Note the use of the PostgreSQL ARRAY[...] constructor notation, and note that the 'string literals' containing the json objects are inside the array.

This is a single jsonb field, containing an array of objects:

test=> SELECT 
'
        [
                { "x": 1, "y": 2 },
                { "a": 3, "b": 4 }
        ]
' :: jsonb;
                jsonb                 
--------------------------------------
 [{"x": 1, "y": 2}, {"a": 3, "b": 4}]
(1 row)

Note that it's a single string literal containing a json-serialization of an array of objects.

how to set type for jsonb[] for play-scala anorm pgsql

1 Answers