How to set type for jsonb [] for play-scala anorm pgsql
I am creating a polling application in scala / play framework and am using postgres9.4 and anorm. I am using jsonb as datatype elsewhere, but in one place I want to use jsonb [] thinking that this type is an array of jsonb values. My json structure looks like this:
[
{"guitar":{"passion":3,
"expertise":5,
"willingToTeach":false,
"lookingForOthers":false
}
},
{"soccer":{"passion":3,
"expertise":3,
"willingToTeach":true,
"lookingForOthers":true
}
}
]
Every interest here is a json structure. I was able to add json response values to other columns in pgsql using jsonb
as datatype, but when I try to use jsonb[]
I get complaints: [PSQLException: Unknown type jsonb[].]
In pgadmin3 it literally shows my this exact datatype: jsonb[]
for the column I'm trying to insert. In my anomaly source code, I tried to set 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've tried this, but I can't even find that all the string values I can use as an argument to pgObject.setType (). I also don't know how I would give the expression from jsonb
to in jsonb[]
any other way than setting the type using the setType () method.
Any help would be greatly appreciated.
source to share
Many frameworks do not support SQL arrays. There is a SQL standard for arrays, but most clients have partial or no support for it.
PostgreSQL type[]
has an array type
. So, jsonb[]
is an array of jsonb
. Your client doesn't seem to understand this: either it doesn't support arrays, or it does support special data-driven array support for each data type.
Fortunately, you don't need a SQL array from jsonb
because it jsonb
stores JSON JavaScript Serial Object Notation, JSON defines both objects and arrays. This way you can have an array of objects inside the field jsonb
. No need for SQL arrays.
This is a SQL array of values jsonb
, where each value jsonb
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 a data type jsonb[]
. Note the use of PostgreSQL constructor notation, ARRAY[...]
and note that 'string literals'
containing the objects json
is inside an array.
This is one field jsonb
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 this is the only string literal containing json serialization of an array of objects.
source to share