I am trying to get some test data in to a column inside a PostgreSQL table, seems simple enough but have tried the following methods using pgAdmin 4 and nothing works. It is simply an array of JSON objects that will later be read in by a spring entity it is mapped to.
- Simply using the GUI to double click a box in a row under the column
Expected - saves the JSON array
Actual - can not save
[{
"appName": "UCRM",
"scopes": [
"read",
"write"
]
},
{
"appName": "OCTA",
"scopes": [
"read",
"write",
"delete"
]
}]
error after trying to save the above JSON
- using a script to insert the same data to every row that has that column
Expected - saves JSON array to every column "app_acl" for every row
Actual - It does insert in to the table but, saves a invalid JSON array and making it useless as shown below
script in question:
UPDATE application_acl SET app_acl = array[
'{
"appName": "UCRM",
"scopes": [
"read",
"write"
]
}',
'{
"appName": "OCTA",
"scopes": [
"read",
"write",
"delete"
]
}'
]::json[];
output:
{"{
\"appName\": \"UCRM\",
\"scopes\": [
\"read\",
\"write\"
]
}","{
\"appName\": \"OCTA\",
\"scopes\": [
\"read\",
\"write\",
\"delete\"
]
}"}
jsonbinstead ofjson[].json[], which is exactly what you told it to store. Your ORM should be able to decode that (like any other postgres array), but yes, you really don't want that. Usejson/jsonbinstead.jsonandjsonbare serialised as JSON text. But regardless of the (binary) wire format, are you not using a database library that handles this for you?json[]) almost never makes sense. It's better to store a JSON array in ajsonb(or at leastjson) column.