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\"
    ]
  }"}
6
  • 1
    Start by using jsonb instead of json[]. Commented Jan 2, 2023 at 7: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? Commented Jan 2, 2023 at 7: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. Commented Jan 2, 2023 at 7:02
  • "This will save the raw binary data of the json instead correct?" - not quite, see also here and there. "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? Commented Jan 2, 2023 at 7: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. Commented Jan 2, 2023 at 7:58

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.