Save List as JSON array in postgresql

Question

I have a List private List<Lesson> lessons; and I need to save this list to PostgreSQL database column with data type _json or json[] (JSON array). But I get one of these errors:

ERROR: column "lessons" is of type json[] but the expression is of type character varyingorERROR: malformed array literal: "[{"id":17,"title":"Lesson 1","part":1.0}]

How correctly serialize List to get the correct format of Postgresql JSON array?

Answer 1

You might need to use JsonBinaryType class from com.vladmihalcea:hibernate-types library and apply json (or jsonb) to the column definition:

// LessonList.java
import com.vladmihalcea.hibernate.type.json.JsonBinaryType;
import java.util.List;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.Table;
import lombok.AllArgsConstructor;
import lombok.Builder;
import lombok.Getter;
import lombok.NoArgsConstructor;
import lombok.Setter;
import org.hibernate.annotations.Type;
import org.hibernate.annotations.TypeDef;

@Entity
@Table(name = "lessons")
@Getter
@Setter
@Builder
@AllArgsConstructor
@NoArgsConstructor
@TypeDef(name = "json", typeClass = JsonBinaryType.class)
public class LessonList {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Integer id;

    @Type(type = "json")
    @Column(columnDefinition = "json")
    private List<Lesson> lessons;
}

SQL Script to create the table:

CREATE TABLE "lessons" (
   "id" Serial NOT NULL,
   "lessons" JSON NOT NULL,
   "created_at" Timestamp Without Time Zone DEFAULT NOW() NOT NULL,
   PRIMARY KEY ("id")
);

The rest of the classes are to provide working example:

// Lesson.java
@Getter
@Setter
@Builder
@AllArgsConstructor
@NoArgsConstructor
@ToString
public class Lesson {
    private int id;
    private String title;
    private double part;
}

---

A repository extending Spring CrudRepository interface and DAO class are trivial.

A command line runner to run test:

@Slf4j
@Component
public class TestJson implements CommandLineRunner {

    @Autowired
    private LessonListDao dao;

    @Override
    public void run(String[] args) {

        List<Lesson> lessons = Arrays.asList(
                Lesson.builder().id(11).title("Physics").part(1.0).build(),
                Lesson.builder().id(12).title("Chemistry").part(2.0).build(),
                Lesson.builder().id(13).title("Biology").part(3.0).build()
        );

        LessonList list = LessonList.builder().lessons(lessons).build();

        LessonList result = dao.save(list);

        LOGGER.info("result: " + result);

        List<LessonList> all = dao.findAll();
        all.forEach(a -> LOGGER.info("item #" + a.getId() + "; lessons=" + a.getLessons()));
    }
}

Output:

lesson.TestJson     : result: lesson.LessonList@6166aac5
lesson.TestJson     : item #1; lessons=[Lesson(id=1, title=Math, part=1.0), Lesson(id=2, title=English, part=2.0), Lesson(id=3, title=Informatics, part=3.0)]
lesson.TestJson     : item #2; lessons=[Lesson(id=11, title=Physics, part=1.0), Lesson(id=12, title=Chemistry, part=2.0), Lesson(id=13, title=Biology, part=3.0)]

DB Data: SELECT * FROM "public".lessons:

**id**          **lessons**                                     **created_at**
[PK] Integer    json                                            timestamp without timezone
1               [{"id":1,"title":"Math","part":1.0},            2020-06-03 18:08:55.948007
                {"id":2,"title":"English","part":2.0},
                {"id":3,"title":"Informatics","part":3.0}]
2               [{"id":11,"title":"Physics","part":1.0},        2020-06-03 18:27:06.565191
                {"id":12,"title":"Chemistry","part":2.0},
                {"id":13,"title":"Biology","part":3.0}]

Update

This library does not support json[] type because it could be rather redundant.

The example below uses plain JDBC + ObjectMapper to handle specific PostgreSQL array of json Main point is that you need to use getArray method of ResultSet and then convert the String values inside PGobject to your object

ConfigurableEnvironment environment = (ConfigurableEnvironment) applicationContext.getEnvironment();
DataSource ds = DataSourceBuilder.create()
.username(environment.getProperty("ds.pgsql.username"))
.password(environment.getProperty("ds.pgsql.password"))
.url(environment.getProperty("ds.pgsql.url")).build();

try (Connection connection = ds.getConnection();
    PreparedStatement ps = connection.prepareStatement("SELECT id, lessons FROM lesson");
    ResultSet rs = ps.executeQuery()) {
        while (rs.next()) {
            int id = rs.getInt("id");
            Object[] arrLessons = (Object[]) rs.getArray("lessons").getArray();

            List<Lesson> jsonLessons = Arrays.stream(arrLessons)
                                             .map(PGobject.class::cast)
                                             .map(lesson -> convert(lesson.getValue()))
                                             .collect(Collectors.toList());

            System.out.println(id + "; converted: " + jsonLessons);
        }
}
//...
private Lesson convert(String value) {
    try {
        return mapper.readValue(value, Lesson.class);
    } catch (JsonProcessingException e) {
        throw new IllegalArgumentException(e);
    }
}

Output

1; converted: [Lesson(id=1, title=Math, part=0.0), Lesson(id=2, title=English, part=0.0)]
2; converted: [Lesson(id=3, title=Physics, part=0.0), Lesson(id=4, title=Chemistry, part=0.0)]