0

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.

  1. 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

  1. 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\"
    ]
  }"}
FishOnAComputer
  • 15
  • 5
  • 1
    Start by using `jsonb` instead of `json[]`. – Bergi Jan 02 '23 at 07:00
  • This will save the raw binary data of the json instead correct? if I do this does it have to be reconstructed in my java code when reading? – FishOnAComputer Jan 02 '23 at 07:02
  • 1
    "*saves a invalid JSON array and making it useless as shown below*" - no. This is the proper representation of `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. Use `json`/`jsonb` instead. – Bergi Jan 02 '23 at 07:02
  • "*This will save the raw binary data of the json instead correct?*" - [not quite](https://stackoverflow.com/q/68820299/1048572), see also [here](https://stackoverflow.com/questions/39637370/difference-between-json-and-jsonb-in-postgres) and [there](https://stackoverflow.com/questions/22654170/explanation-of-jsonb-introduced-by-postgresql). "*if I do this does it have to be reconstructed in my java code when reading?*" - no. Both `json` and `jsonb` are serialised as JSON text. But regardless of the (binary) wire format, are you not using a database library that handles this for you? – Bergi Jan 02 '23 at 07:05
  • 1
    An array of JSON (`json[]`) almost never makes sense. It's better to store a JSON array in a `jsonb` (or at least `json`) column. –  Jan 02 '23 at 07:58
  • Bergi, changing it to jsonb worked well to be able to store the array. Now I just have to figure out how to use that in my spring boot entity class. Thanks! – FishOnAComputer Jan 02 '23 at 08:44

0 Answers0