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?

• Dumidu Udayanga: Can you update the question what you tried so far?
• Volodymyr Zavada: @DumiduUdayanga I tried using ObjectMapper and Gson library

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)]

• Volodymyr Zavada: thanks for your answer, but I tried this solution and not works with column type JSON array(Postgresql json[]). Your solution can be applied to column JSON, not JSON array
• Nowhere Man: JSON array json[] is not supported with this library. You may check the update with the specific fix.