How can I save a JSON object in a separate type?

Question

I've been trying to save a JSON array in Postgres using Java and a PreparStatement for half an eternity.

I use my own type in Postgres.

Unfortunately I always get a type error message

Here's my Postgres Function to insert data into my table:

CREATE OR REPLACE FUNCTION "Portfolio"."Einfuegen"(
"dokumenteIN" "Portfolio".t_dokumente[],
"finanzenIN" numeric[],
"bildIN" bytea)
RETURNS character varying
LANGUAGE 'plpgsql'

COST 100
VOLATILE 
AS $BODY$
BEGIN
  INSERT INTO "Portfolio"."Historie" (dokumente,finanzen,bild) VALUES 
("dokumenteIN","finanzenIN","bildIN");
return 'Gesichert';
END;
$BODY$;

And my custom type looks like this:

 CREATE TYPE "Portfolio".t_dokumente AS
(
 inhalt json[]
);

ALTER TYPE "Portfolio".t_dokumente
OWNER TO postgres;

I'm trying to insert some JSON Array data with Java:

    for (int i = 0; i < arrayDokus.length(); ++i) {

        JSONObject rec = arrayDokus.getJSONObject(i);
        String nameBild = rec.getString("Name");
        String inhalt = rec.getString("Inhalt");

        byte[] in = inhalt.getBytes();

        try {
            Blob blob = new SerialBlob(in);

            JSONObject neuesObjekt = new JSONObject();
            neuesObjekt.append("Name", nameBild);
            neuesObjekt.append("Inhalt", blob);

            where.add(neuesObjekt);
            
            

        } catch (SerialException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }

    }

   
    PGobject jsonObject = new PGobject();
    jsonObject.setType("json[]");
    jsonObject.setValue(where.toString());


    PreparedStatement stmt = con.prepareStatement("SELECT * FROM \"Portfolio\".\"Einfuegen\" 
   (?::\"Portfolio\".t_dokumente[],?,?)");
            
        
            stmt.setObject(1, jsonObject);
            stmt.setArray(2, f);
            stmt.setBinaryStream(3,in);
            ResultSet rs = stmt.executeQuery();
            con.commit();

          [...]

But i got the error:

org.postgresql.util.PSQLException: ERROR: Can't convert type json [] to type "Portfolio" .t_dokumente []

Unrelated to your problem, but: you should really avoid those dreaded quoted identifiers. They are much more trouble than they are worth it. wiki.postgresql.org/wiki/… — user330315
– user330315, Commented Aug 24, 2020 at 18:01
Your data structure is fatally strange, column dokumente is an array of complex, and each complex has one field inhalt - an array of jsons, why not use just JSONB ? — Игорь Тыра
– Игорь Тыра, Commented Aug 24, 2020 at 18:12

Schwern · Accepted Answer · 2020-08-24 19:26:58Z

"dokumenteIN" "Portfolio".t_dokumente[] says you want a Postgres array of t_dokumente which is itself json[]. That makes it json[][]: a Postgres array of Postgres arrays of JSON. Something like array[['{"foo": 23}'::json]].

But "Portfolio".t_dokumente[] is a composite type with a single field: inhalt. I'm not sure how to make that work.

This is unnecessarily complicated. JSON already supports arrays and key/value pairs. Simplest thing to do is to just use jsonb: dokumenteIN jsonb. jsonb, not json, because it's overall far more efficient.

The major practical difference is one of efficiency. The json data type stores an exact copy of the input text, which processing functions must reparse on each execution; while jsonb data is stored in a decomposed binary format that makes it slightly slower to input due to added conversion overhead, but significantly faster to process, since no reparsing is needed. jsonb also supports indexing, which can be a significant advantage.

Then you can use JSON arrays of JSON objects: '[{"inhalt": "first"}, {"inhalt": "second"}]'.

Collectives™ on Stack Overflow

How can I save a JSON object in a separate type?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related